Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
QuantumBunny
Starting Member
2 Posts |
 Posted - 2006-10-06 : 18:51:33
|
| I have looked around all over the web searched many forums, and I am out of things to try before I try ADO.NET and BCP. Though BCP has had big issues in the past across my requirements. Issue:In code(happens to be VB .NET), I want to copy a table and all of its data, from one database to another. This will be a scheduled job that occurs daily, weekly, or monthly depending on the users' requests. Current Workaround: 1) Get table columns2) Select * from tbl in chunks of 50,000, saving the last primary key3) Write a file line for each row4) Get next 50,000 based off last primary key. * Repeat 3 and 4 until done5) Drop table in destination DB6) Create table in destination DB7) Bulk insert the data in destination DBDownside: This is fine, except it is terribly slow over a few million to tens of millions of records. Part of this is because of the ordering and chunk selecting, but even with our biggest servers, selecting all records at once into a datatable is a terrible operation, and doesn't work at all for some of our larger tables.Difficulty: I need this solution to work in SQL Server 2000, SQL Server 2005, and Oracle.(At least, ANSI for support of all would be best).Anyone got recommendations from similar situations? |
|
|
|
|
|
|
|
Table from DB1 to DB2