[RESOLVED] Batch insert into DB?

[juliemac]

I created a recordset from one table/DB Is there a way to append this data to another table/DB in a batch load with out single stepping through the recordset? (read/insert, movenext, read/insert)

Table definitions are exactly the same.

[si_the_geek]

The most efficient way is not to use a recordset at all, but simply transfer all the data you want with a single SQL statement (assuming that it is supported by your database system(s)).

What are the database systems on each end?

It would probably help us to see the table definition too.

[juliemac]

Oracle 8i to Sequel Server 7. I created the tables exactly (well as much as possible) for a datamart as the network is slow here.

[si_the_geek]

I haven't use Oracle enough to know what functionality it provides, and SQL Server 7 is before my time (I've mainly used 2000).

If this is a one-off job then it would be worth looking in Enterprise Manager to see if there is functionality to import the data (presumably via DTS).

If it is going to happen regularly it would probably be best to treat the Oracle system as a Linked Server, but I'm afraid you'll need to look into the documentation for how this works on SQL Server 7.


If that doesn't help, I'm guessing the best way would be to disconnect the recordset and re-connect it to the other server (hopefully somebody will post to say how!), otherwise for a (minor?) speed improvement you can use the batch update functionality of ADO, as seen here.

[salvelinus]

One idea I came up with when I couldn't figure out a query to do what I wanted was to loop through recordsets to build a string with the data wanted, create a text file, and then import that file.
I don't know if that was the best solution, but it was a lot faster than running K's of INSERT statements. Plain SQL would've been better, but like I say, never figured out a way to do it.

[techgnome]

Two words: BULK INSERT....

Another technique I've used... is to create a disconnected recordset, add everything to it, then use the UpdateBatch method to send all the changes to the database.

-tg

[juliemac]

The initial pull involved thousands of entries in multiple tables. Once done I am checking the datestamp of the reciever DB, checking the donor for any entries greater, then making a disconnected RS from the donor DB. Transfer and loop through the data. Done every 1/2 hour, keeps transfer size low, server loading minimal and data fairly up todate.

Thanks for the help guys!