How to Overwrite Table with Data from Another Table in SQL

sql server

Our users want to refresh a QA database from production but want two tables (lets call them T1 and T2) to retain the original QA data. So I copied two tables from QA (DB1) to a temp QA database (DB2). Then refreshed DB1 from production. After the refresh, I want to overwrite T1 and T2 data from DB2 to DB1 so it can contain pre-refresh QA values.

I have done the following:

  1. Use 

    select * 
INTO D1.dbo.T1
FROM D2.dbo.T1

  2. Then refreshed D1 from prod

  3. Then truncate T1 with the following step:

    SELECT COUNT(*) AS BeforeTruncateCount
FROM T1;
GO
TRUNCATE TABLE T1;
GO
SELECT COUNT(*) AS AfterTruncateCount
FROM T1;
GO

  4. Now when I go back to copy data from D2.T1 to D1.T1, I get the error that there is already an object named T1 in the database.

Should I drop the table and copy?

Or is there any better method for the whole procedure?

Best Answer

To combine the answers from @Kin and @DavidSpillett.

First an assumption. DB1 and DB2 are on the same instance. It certainly sounds like they are but it never hurts to state the obvious (particularly since I've been wrong with what I thought was obvious before.)

Given that, if the data is large, say multiple millions of rows then use an SSIS package. This has one specific feature that you will want to use. You can specify a batch size. This way your transaction size will be smaller. The downside to the SSIS package is that it adds a level of complexity to your process. It isn't a big one, but if you are working with a smaller table you don't need it.

Which leads us to the other option. If however the data is in the several hundred thousand range or smaller then use code like this.

USE D1;
GO
TRUNCATE TABLE dbo.T1;
GO
INSERT INTO D1.dbo.T1
SELECT * 
FROM D2.dbo.T1;
GO

And last but not least it if it is somewhere on the boarder then I would try both and see what works best for you.

NOTE: You might also consider not using a SELECT * on the INSERT INTO and instead specify a field list.

That structure would look like this

INSERT INTO D1.dbo.T1 (Field1, Field2)
SELECT Field1, Field2
FROM D2.dbo.T1;
Related Question