Sql-server – Copy huge amount from 1 table to other

bulkcopyperformancequery-performancesql-server-2005

I have two tables with same structure I need to copy over all tables from table A to table B, the problem is that some records from table A already exist in table B so that made the Import Fail.

So I made a query to do the import (Also using the build in Import tool) like that 

SELECT * from TransactionEntryN
WHERE TransactionEntryN.TransactionEntryID 
NOT IN (select TransactionEntryID FROM TransactionEntry)

The problem is that this operation takes 13 min. to copy just 50K records and I have 16 Million records there, it would take me a week to finish that…

Is there any faster way to do it?

btw the primary key TransactionEntryID is a uniqueidentifier that may slow it down? (I can't change it I'm just wondering if that the issue.

Best Answer

If you want the second table to contain the same data as the first table, why not simply wipe the table out and replace it?

BEGIN TRANSACTION;

  DROP TABLE TransactionEntry;

  SELECT * INTO dbo.TransactionEntry FROM dbo.TransactionEntryN
    -- other WHERE clauses?
  ;

COMMIT TRANSACTION;
-- create indexes / permissions etc.
Related Question