Solved

Inserting multiple rows into SQL Server 6.5

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

747 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

14 Experts available now in Live!

Get 1:1 Help Now