Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Try where not exists and make sure the TransactionEntryID is indexed in both tables. –  HLGEM Mar 21 '12 at 15:12
    
"all tables from table A to table B"? –  Aaron Bertrand Mar 21 '12 at 15:14
    
yes, but only if there's not the same record already there (since I copied some already and other records where added to table B since.) –  Ezi Mar 21 '12 at 15:17
    
No there not indexed, should I add a clustered or a non clustred index? –  Ezi Mar 21 '12 at 15:24
1  
I won't put this as an answer but just a suggestion for you: Since I'm reading that you need to merge datasets (possibly in both directions if records are missing on either side?) you could consider a tool like Redgate's data compare. For a dataset that large you'd probably need a large temp area for the util if you went that way, but it does simplify tasks like this if you don't want to or don't have the time to work out the T-SQL. Good luck. –  Eric Higgins Mar 21 '12 at 19:00

2 Answers 2

up vote 3 down vote accepted

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.
share|improve this answer
    
the problem is that some records where added already in table B that are not in table A so this option is not available anymore... its too late –  Ezi Mar 21 '12 at 15:22
    
Maybe you could isolate those rows and store them elsewhere first? This is probably still most efficient if it is a small number. –  Aaron Bertrand Mar 21 '12 at 15:30
    
sounds like this would be my best (only) solution –  Ezi Mar 21 '12 at 22:55

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();
                    }
                }
            }
        }
    }
share|improve this answer
1  
Getting all that data over the network to the client and back to the server in inefficient. –  AlexKuznetsov Mar 28 '12 at 2:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.