cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ad...@implements.be
Subject Re: Set SQL results into an array
Date Mon, 20 Jan 2003 10:05:51 GMT
Why don't you write a stored procedure on SQL Server ?
Something like

create procedure myProc as

declare myCurs cursor for "your first select that gets the array"

open myCurs
fetch from myCurs into ....
while (@@fetch_status = 0)
  begin
    insert into ......
    fetch next from ....
  end


This is an example of such a stored procedure.  It writes values from one query 
into another ....

CREATE PROCEDURE spADMINExportTranslations
as
	declare @ID int
	declare @Text varchar(7000)
	declare @Line varchar(7000)

	delete from EXPORTTranslations

	declare TCursor cursor for
		select ID, Text
			from Translations
			where id % 100000 between 1 and 74999
			and len( text ) <= 250
			order by id
	
	open tcursor

	fetch next from TCursor into @ID, @Text

	while (@@fetch_status = 0)
	begin
		set @Text = replace (@Text, '''', '''' + '''')

		set @Line = 'INSERT INTO Translations (ID, Text) VALUES (' 
		set @Line = @Line + cast(@ID as varchar(10)) 
		set @Line = @Line + ', ' + '''' + @Text + ''''
		set @Line = @Line + ')'

		insert into ExportTranslations (line) values( @line)

		fetch next from TCursor into @ID, @Text
	end

	close TCursor

	deallocate TCursor

GO

Regards,
Yves

  



Citeren Maxime.Gheysen@swisscom.com:

> Hi everybody,
> I use microsoft SQL server, and XSP pages. I also use ESQL for all my DB
> commands.
> 
> I want to insert data with query like "INSERT Into blabla (ID) values
> (MyArray)"
> In this query "MyArray" is the result of another query. How can I make
> it?
> Here is an example :
> 
> 
> Table : XY
> Columns : ID, Num, Date, Descript
> 
> Table : blabla
> Columns : ID, Num_ID, Num_text
> 
> 
> First I select some entries from table XY :
> Select *
> From xy
> Where Descript LIKE '%abc'
> 
> 
> Then I want to put them into an array (oARRAY) and make a query to
> insert entries with these paramters.
> INSERT INTO blabla
>                       (Num_ID, Num_text)
> VALUES     (oARRAY[1], 'abc')
> INSERT INTO blabla
>                       (Num_ID, Num_text)
> VALUES     (oARRAY[2], 'abc')
> ...
> 
> Or do someone have another solution?
> Thanks a lot for your help!
> 
> ---------------------------------------------------------------------
> Please check that your question  has not already been answered in the
> FAQ before posting.     <http://xml.apache.org/cocoon/faq/index.html>
> 
> To unsubscribe, e-mail:     <cocoon-users-unsubscribe@xml.apache.org>
> For additional commands, e-mail:   <cocoon-users-help@xml.apache.org>

---------------------------------------------------------------------
Please check that your question  has not already been answered in the
FAQ before posting.     <http://xml.apache.org/cocoon/faq/index.html>

To unsubscribe, e-mail:     <cocoon-users-unsubscribe@xml.apache.org>
For additional commands, e-mail:   <cocoon-users-help@xml.apache.org>


Mime
View raw message