Copy huge amount from 1 table to other

Question

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.

Answer 1

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.

Answer 2

Remove all the rows that are in table B and not in table A. Then you could use DOTNET SQLBulkCopy to copy data from table A to table B. It is pretty fast. For 100,000 records it would take less than 10 seconds. I am writing some pseudo code here. If you want a complete working example you could go to http://technico.qnownow.com/2012/03/27/using-sql-bulk-copy-to-efficiently-load-data-from-source-db-to-destination-db/

// Open a sourceConnection
        using (SqlConnection sourceConnection =
                   new SqlConnection(GetSourceConnectionString()))
        {
            sourceConnection.Open();

            // Get the data from the source table as a SqlDataReader.
            SqlCommand commandSourceData = new SqlCommand(
                @"SELECT *                   
                FROM dbo.tableA;", sourceConnection);
            SqlDataReader reader =
                commandSourceData.ExecuteReader();

             // Open the destination connection. 
            using (SqlConnection destinationConnection =
                       new SqlConnection(GetDestinationConnectionString()))
            {
                destinationConnection.Open();

                using (SqlBulkCopy bulkCopy =
                           new SqlBulkCopy(destinationConnection))
                {
                    bulkCopy.DestinationTableName =
                        "dbo.tableB";

                    try
                    {                            
                        bulkCopy.WriteToServer(reader);
                    }
                    finally
                    {                            
                        reader.Close();
                    }
                }
            }
        }
    }