I am copying data from one database to another having similar tables using Insert....select.

Now, it's ok but if the source db has edited something and I want it to be updated in the target database table then that would be a problem.

I have few solutions:

1. If record exists (would verify using a GUID we have) then Delete it and its child data and Insert all again hence new updated data.

2. Taking union of the tables

   SELECT * FROM SourceDb.dbo.Table1
   UNION
   SELECT * FROM TargetDb.dbo.Table2
   

If you get records greater than any of two tables, they don't have same data and needs to be deleted and re-add.

But problem is I have around 1 parent and around 7 child table so taking and checking using union would take me some extra coding.

what should I do?

CodePudding user response：

The general query to verify that set A = set B is :

A - B U B - A = ∅ (set theory)

You can use the above query :

WITH
TA AS (SELECT * FROM databaseA.SchemaA.TableA),
TB AS (SELECT * FROM databaseB.SchemaB.TableB)
SELECT * FROM TA
EXCEPT
SELECT * FROM TB
UNION ALL
SELECT * FROM TB
EXCEPT
SELECT * FROM TA;