Multiple Inserts in a Stored Procedure
-
Is there any way to do a multiple inserts in a stored procedure in SQL2000? Please help. Thanks.
RJS wrote: Is there any way to do a multiple inserts in a stored procedure in SQL2000? You could write a while loop inside the stored procedure, there are many ways of doing something like this. Nick Parker
May your glass be ever full. May the roof over your head be always strong. And may you be in heaven half an hour before the devil knows you’re dead. - Irish Blessing
-
Is there any way to do a multiple inserts in a stored procedure in SQL2000? Please help. Thanks.
-
Can you give more details? Are these just INSERTS into multiple tables? Are the INSERTS dependent on each other? Cheers, Simon "Sign up for a chance to be among the first to experience the wrath of the gods.", Microsoft's home page (24/06/2002)
Simon, thanks for the reply. The scenario is like this: I have (let's say) 3 records or more in my .net page. Example: "John", "123 Anywhere", "City1" "Rudy", "345 Anywhere", "City2" "Tom", "335 Anywhere", "City2" ... ... Is it possible to do a multiple inserts in a stored procedure to save those 3 records or more in a table? If so, how do I pass the parameters since I will not know what is the maximum records that will be sent at once? Thanks. :)
-
Simon, thanks for the reply. The scenario is like this: I have (let's say) 3 records or more in my .net page. Example: "John", "123 Anywhere", "City1" "Rudy", "345 Anywhere", "City2" "Tom", "335 Anywhere", "City2" ... ... Is it possible to do a multiple inserts in a stored procedure to save those 3 records or more in a table? If so, how do I pass the parameters since I will not know what is the maximum records that will be sent at once? Thanks. :)
What you would like to do is the basically pass a recordset to a stored procedure in SQL Server 2000. (?) The only way I know of to do this is to create an Xml file, pass it as a text (ntext in .Net :) ) parameter to the stored procedure. And then use the OPENXML SQL statement, which enables you to treat an XML file as a table (check OPENXML in Books Online). You can then easily use something like INSERT INTO MyTable (val1, val2, val3) SELECT val1, val2, val3 FROM OPENXML(...) It very performant and works like a charm! :) Hope this helps, Morty
-
What you would like to do is the basically pass a recordset to a stored procedure in SQL Server 2000. (?) The only way I know of to do this is to create an Xml file, pass it as a text (ntext in .Net :) ) parameter to the stored procedure. And then use the OPENXML SQL statement, which enables you to treat an XML file as a table (check OPENXML in Books Online). You can then easily use something like INSERT INTO MyTable (val1, val2, val3) SELECT val1, val2, val3 FROM OPENXML(...) It very performant and works like a charm! :) Hope this helps, Morty
That's one way, Morten. Another, which is very dependent on the type of data you're passing through, could pass a delimited list (a big string) to the stored proc and use some of the following code:
DECLARE @Array varchar(50) DECLARE @iStart int DECLARE @iEleFound int DECLARE @iMaxEleLen int DECLARE @iNextDelimIndex int DECLARE @iEleLen int SET @Array = '1,2,3,4,5,6,7,8,9,65,66,67,100,101,102,654321' SET @iStart = 0 SET @iEleFound = 0 SET @iMaxEleLen = 5 SET @iNextDelimIndex=0 SET @iEleLen =0 print '---------' print 'LENGTH of string: ' + CAST(LEN(@Array) AS VARCHAR) while CHARINDEX ( ',' , @Array,@iStart ) > 0 -- for each element in the array begin SET @iEleLen =1 --default --if not ',' or '' if (CAST(SUBSTRING(@Array, @iStart + 1, 1) AS char(1)) <> ',') and not(len (SUBSTRING (@Array, @iStart + 1, 1)) = 0) begin--element found at this index SET @iNextDelimIndex = CHARINDEX ( ',' , @Array,@iStart+1) if(@iNextDelimIndex=0) begin SET @iNextDelimIndex = CAST (LEN(@Array) AS VARCHAR)+1 print 'latest element is next:' end SET @iEleLen = @iNextDelimIndex - @iStart --DB function would occur here print 'NEXT DELIM LOCATION-->' + CAST(@iNextDelimIndex AS VARCHAR(10)) print 'VALUE FOUND-->' + RTRIM( CAST(SUBSTRING(@Array, @iStart + 1, @iNextDelimIndex - @iStart-1) AS char(10)) ) + '<--' SET @iEleFound = @iEleFound + 1 end else --no element found at this index begin print 'do nothing' end SET @iStart = @iStart + @iEleLen end --if (CAST(SUBSTRING(@Array, @iStart + 1, 1) AS char(1)) <> ',') and not(len (SUBSTRING (@Array, @iStart + 1, 1)) = 0) print '------ ELEMENTS FOUND: ' + CAST(@iEleFound AS CHAR(2)) + ' ---------'Note: not fully tested, but should give you a starting point. Cheers, Simon "Sign up for a chance to be among the first to experience the wrath of the gods.", Microsoft's home page (24/06/2002)