Solved

Inserting multiple rows into SQL Server 6.5

Posted on 2001-06-26
4
476 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

862 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now