[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Inserting multiple rows into SQL Server 6.5

Posted on 2001-06-26
4
Medium Priority
?
488 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 400 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

656 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