Insert 300 records in 1 transaction/round-trip to
-
HI ALL
I get a array in vb of lets say 300 numeric elements. For each numeric element I should insert a record to a 1-column table and assign the element value to that column.
I am looking for the best way to implement it. I have to ideas and I do not know how to implement them.
1.Let's say I have the stored procedure sp_InserNumberToTable1. I want all the 300 calls of this stored procedure to be executed in one round-trip to the server.
Can I perform the following statement?:
dim cmd1 as adodb.command
cmd1.activeconnection=…
cmd1.execute " sp_InserNumberToTable1 (1) sp_InserNumberToTable1 (4) sp_InserNumberToTable1 (8)…"
or should I use begintrans(ADO) method?
2.maybe there is a way to create a stored procedure that will get unknown number of parameters, and loop through all of parameters and insert the a record for each one?
I would very thankful if someone will help me to implement this???? I prefer option 2…
Thanks!!!
Thanks!!! -
Try a comma delimited list as one parameter and strip it in the stored procedure
-
I agree with the number guy. Just submit to an SP as a VARCHAR comma delimited. Then you can use CHARINDEX to seperate and insert using a WHILE loop to work thru it. You will however still have several inserts but only one server trip.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Try using an XML document, then do the insert using OpenXML.
Andy
-
HI ALL
I understand from your posts that the only way to pass unknown number of numbers is by using a string parameter. Unfortunately varchar data type was not limited, cause the end-user may want to insert even 2,000 records and theoretically even 20,000. So that means in some cases Will have to split the execute calls, and I do not like to work that way, do I have a choice?
Can't I send more than 1 statement at the same time? May by using a script or something? Or maybe by sending a line with statements delimited by a character?
Thanks!!!
Thanks!!! -
You can send the data to the SP as TEXT, that way you have 32 MB/GB+? to play with.
-
HI
But SQLSERVER2000 does not let my declare a text parametter
Thanks!!!
Thanks!!! -
At some point trying to send the data via a proc call is a bad idea anyway. For a reasonable sized set (you pick what is reasonable) XML will work. After that probably more efficient to make it a batch process - create the data as a file (BCP, XML, custom) and have a loader process (DTS, EXE, whatever) pick it up and process.
Andy
-
Hmmmmmm,
insert 20.000 records in one roundtrip......
doesn't make sense to me. I think that the client doesn't
type those records but it is some kind of resultset i suppose....
Well i would leave the resultset in a kind of temp table on the server and then issue
a transaction that would insert all the records on the server.
What i am trying to say is try to build the resultset on the server
and simply insert them on the server.
An other method could be to dump the records in a textfile on your local disk
then transfer the textfile to the server.
USE a DTS package to read the textfile and insert the records to the database.
This certeinly reduces the number of round trips ann can handle as much records as you like.
........
-
OPENXML is the neatest solution as it does not require any new functions and performs better than a custom user defined function.
If you want to go down the userdefined route then you can declare function and procedure variables as text, this allows you to parse an unlimited length string.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
10X FOR U ALL!!!
1. about the XML, I do not know even what is XML, so I think I'll pass, plz dont kill me
.2. simonsabin, I did not succeded to declare a local variable or a parametter of a function as TEXT?
Thanks!!!
Edited by - nitz on 10/12/2002 6:45:37 PM
Thanks!!! -
XML is a hierarchy structure file. Lots of examples on the web and you can use various providers to persist data to files, I do this for configuration data on several apps.
Do you have any idea of what will be your largest possible submission as far as number of characters? Otherwise I suggest someone help with the XML stuff as I don't have anything tied to SQL itself I can give as an example. Or I will see if I can find something to help better.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
This are the 2 functions,
so you can do something like
SELECT * FROM dbo.SplitTokenString ('1,2,3,4,5,',',')
which will return
1
2
3
4
5
IF EXISTS (SELECT 1 FROM sysobjects WHERE name='SplitTokenStringPositions')
DROP FUNCTION dbo.SplitTokenStringPositions
GO
CREATE FUNCTION dbo.SplitTokenStringPositions (
/*******************************************************************************
Written By : Simon Sabin
Date : 12 October 2002
Description : Returns the start and length of the tokens in a string
History
Date Change
------------------------------------------------------------------------------
12/10/2002 Created
*******************************************************************************/
@TokenString text
,@Delimiter varchar(100) )
RETURNS @tab TABLE (start int, length int)
AS
BEGIN
DECLARE
@index smallint,
@Start smallint,
@DelimiterSize smallint,
@Finish bit
SET @DelimiterSize= LEN(@Delimiter)
SET @Start = 1
SET @index = 1
SET @Finish = 0
WHILE @Finish = 0
BEGIN
SET @index = CHARINDEX(@Delimiter, @TokenString,@Start)
IF @index = 0
BEGIN
SET @index = DATALENGTH(@TokenString) + 1
SET @Finish = 1
END
INSERT INTO @tab VALUES(@Start ,@Index - 1 - @Start)
SET @Start = @index + @DelimiterSize
END
RETURN
END
GO
IF exists (SELECT * from dbo.sysobjects
WHERE id = object_id(N'[dbo].[SplitTokenString]') )
DROP FUNCTION [dbo].[SplitTokenString]
GO
CREATE FUNCTION dbo.SplitTokenString (
/*******************************************************************************
Written By : Simon Sabin
Date : 12 October 2002
Description : Splits a token string into its tokens
Uses another function to obtain the start and end
positions of each token
History
Date Change
------------------------------------------------------------------------------
12/10/2002 Created
*******************************************************************************/
@TokenString text,
@Delimitervarchar(100) )
RETURNS TABLE
AS
RETURN (
SELECT SUBSTRING(@TokenString, Start ,Length) "id"
FROM dbo.SplitTokenStringPositions(@TokenString ,@Delimiter))
GO
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
.Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply