Inserting multiple rows into SQL Server 6.5

What's the fastest way to insert multiple rows into a table in SQL server 6.5?   I cannot write the data to a text file and then bulk copy it in.  Is there something I can do with an ADO recordset?

The fastest way I can think of is to call a separate INSERT statement for each row that needs to be inserted.  It seems like there should be a faster way.
jgaullAsked:
Who is Participating?
 
morgan_peatCommented:
I've got a little program at the moment that does the same thing (about 62,000 INSERT statements).
The quickest way I found is to build them all up into long strings, and call 'oConn.Execute sSQL'.  Executing about 100 INSERTs in 1 go is a lot quicker (1 network round trip, etc).
You can seperate your INSERTs with a space (at least on MS SQL 2K you can)
Course, you can't use VB BSTR's to do this, because the size of the string is HUGE - you need a string concatenator or something.
My 62,000-odd INSERT statements got completed in 50 minutes, performing one oConn.Execute for each statement (with all my extra calculations) and 20 minutes using the bulk method.

Of course you may get errors in between, so it's wise to use transactions so you can rollback properly...
0
 
sharmonCommented:
Nothing I know of is going to be faster than just doing the INSERT.  Make sure you are using stored procedures to help speed it up.
0
 
DipperCommented:
Use transaction! It's can extremely speed up your program .

Call Begintrans method of connection object before you call execute method , after you insert hundreds of rows , call committrans method at last.
0
 
OtanaCommented:
I'm not sure it's the fastest way but you might try it:

open a recordset using: SELECT TOP 0 * FROM Table1

recordset.add

recordset.fields("name").value="test"

repeat 2 previous steps for every record

recordset.update
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.