HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

overwrite table with data from another table - SQL

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
overwritewithsqlanotherfromdatatable

Problem

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:

-
Use

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


-
Then refreshed D1 from prod

-
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


-
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?

Solution

Best is to use SSIS (data Import/export) to do this. It has the functionality to just transfer selected tables from source to destination. Optionally, you can even save the package so that it can be reused for future/repeated transfers.

EDIT: Adding more screenshots for clarity.

Context

StackExchange Database Administrators Q#47266, answer score: 7

Revisions (0)

No revisions yet.