Copy 84 million rows from one table to another table.
-
Only issue is the tables are on different servers. What would be the best way? I tried looking at BULK INSERT, looks like you can insert into a file. I tried - insert into A select * from B. It is taking for ever, after almost 3 hours of running the query, I had to kill it. Any thoughts/ideas would be appreciated.
-
Only issue is the tables are on different servers. What would be the best way? I tried looking at BULK INSERT, looks like you can insert into a file. I tried - insert into A select * from B. It is taking for ever, after almost 3 hours of running the query, I had to kill it. Any thoughts/ideas would be appreciated.
You've not mentioned which DB this is for, or if it is for two different vendors perhaps. But I would think for table replication involving 84 million rows, you should be investigating table export/import operations associated with the databases. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
You've not mentioned which DB this is for, or if it is for two different vendors perhaps. But I would think for table replication involving 84 million rows, you should be investigating table export/import operations associated with the databases. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Only issue is the tables are on different servers. What would be the best way? I tried looking at BULK INSERT, looks like you can insert into a file. I tried - insert into A select * from B. It is taking for ever, after almost 3 hours of running the query, I had to kill it. Any thoughts/ideas would be appreciated.
-
Only issue is the tables are on different servers. What would be the best way? I tried looking at BULK INSERT, looks like you can insert into a file. I tried - insert into A select * from B. It is taking for ever, after almost 3 hours of running the query, I had to kill it. Any thoughts/ideas would be appreciated.
-
Is this a one-time operation? If so, I'd simply backup the database, restore it on the other server, and do a "select into" in the night. If the tables need to be in sync, I'd probably go for replication.
Bastard Programmer from Hell :suss:
-
Backup and restore sounds like a good idea. Why would I "select * into" again? Also, I read that "select * into" is faster than "insert into select * from"? I guess it would one way, since there will no indexes on the table, right? thanks a lot!
vanikanc wrote:
Why would I "select * into" again?
Also, I read that "select * into" is faster than "insert into select * from"?
I guess it would one way, since there will no indexes on the table, right?I haven't compared them, just a habit of dumping new data in it's own table. If you want to speed up the copy further, then try to have the "source"-database on another diskdrive then the "destination" database.
Bastard Programmer from Hell :suss:
-
Only issue is the tables are on different servers. What would be the best way? I tried looking at BULK INSERT, looks like you can insert into a file. I tried - insert into A select * from B. It is taking for ever, after almost 3 hours of running the query, I had to kill it. Any thoughts/ideas would be appreciated.
-
Only issue is the tables are on different servers. What would be the best way? I tried looking at BULK INSERT, looks like you can insert into a file. I tried - insert into A select * from B. It is taking for ever, after almost 3 hours of running the query, I had to kill it. Any thoughts/ideas would be appreciated.
If it's going to be a regular operation I open a Connection to each database, use a DataReader to read from one, and ExecuteNonQuery to INSERT into the other. A major benefit of this technique is that it allows the app to periodically log its progress (and speed in rows per minute) so you're not flying blind. This technique also allows you to catch and ignore selected Exceptions if you like and not have the whole process die.