Solved

Inserting multiple rows into SQL Server 6.5

Posted on 2001-06-26
4
481 Views
Last Modified: 2013-11-13
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.
0
Comment
Question by:jgaull
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 6

Expert Comment

by:sharmon
ID: 6229499
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
 

Expert Comment

by:Dipper
ID: 6230180
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
 
LVL 1

Accepted Solution

by:
morgan_peat earned 100 total points
ID: 6230379
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
 
LVL 11

Expert Comment

by:Otana
ID: 6234215
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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you don't know how to downgrade, my instructions below should be helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question