patternsqlMinor
overwrite table with data from another table - SQL
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
-
Then refreshed D1 from prod
-
Then truncate T1 with the following step:
-
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?
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.
EDIT: Adding more screenshots for clarity.
Context
StackExchange Database Administrators Q#47266, answer score: 7
Revisions (0)
No revisions yet.