January 21, 2004 at 12:26 pm
I have a large table (17 million rows, 76 columns, 502 bytes) that I need to copy into another table. What is the best method to use to accomplish this task? I am using SQL Server 2000.
I did not want to use the INSERT INTO ... SELECT FROM Statements because it may hog the system and full up the Transaction Log. Is my thinking correct on this?
If I used the BCP Utility I think I would have to first BCP the Table to a Text File and then BCP the Text File into the New Table. I do not think the BCP Utility will allow a table to table copy. Is this correct?
The Bulk Insert Statement only copies a data file into a SQL Table. It does not copy from table to table.
What is an efficient way to get this done? Would the Import\Export Wizard work?
January 21, 2004 at 12:35 pm
You will want to used the Simple or Bulk-Logged recovery model to avoid the overhead of tran logging. The simplest bulk-logged operation would be a SELECT * INTO TableB FROM TableA. Otherwise you are correct that you would need to use a data file (I suggest codepage of RAW, datafiletype of native) as an intermediate step.
--Jonathan
January 23, 2004 at 6:36 am
hi!
if you want - of course this involves some t-sql experience - you can do it using loop for transferring chunks of a specific size by using:
insert into ... select top x from ... where ...
this will help you avoid transactions that are too large and give you a point where you can start over if something fails.
of course you'll have to add a primary-key based restriction to the where clause of your select statement to proceed with the next chunk.
best regards, chris.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply