How to get table in sync using Merge.
I have two tables: Source and Target. I want Target table to be in sync with Source after the query has completed.
Following are the columns for both tables. I want all columns except Contact_no to be in sync with Target table.
Initially I will copy 3 columns data into Target table where Action = 'I'.
Source Table
Unique_ID Part_no Country_Code Contact_no
1 123 IN 12121212
2 456 US 65467987
3 678 CH 65465465
4 897 EN 56546544
Target Table
Unique_ID Part_no Country_Code Action
1 123 IN I
2 456 US I
3 678 CH I
4 897 EN I
After the SQL runs it will update, insert or delete Target table as per Source table with regard to Action flag.
Next Day Source Table changed
Unique_ID Part_no Country_Code Contact_no
1 123 US 12121212 -- Updated Country_Code as "US"
2 456 US 65467987 -- No Change
3 678 CH 56565656 -- Deleted from Source
4 897 EN 56546544 -- No Change
5 114 DL 11111111 -- New Inserted
Target Table
Unique_ID Part_no Country_Code Action
1 123 US U
2 456 US I
3 678 CH D -- Deleted from Source
4 897 EN I
5 114 DL I -- Newly Inserted
I have written below Merge statement but its not working properly, after initial insert with Action flag "I", when I execute, it is updating all the records with action flag as "U" even though I have updated only one record in Source table.
MERGE INTO Target d2
USING (SELECT Unique_id, Part_no, Country_code
FROM Source
UNION ALL
SELECT a.Unique_id, a.Part_no, a.Country_code
FROM Target a LEFT JOIN Source b ON a.Unique_id=b.Unique_id
WHERE b.Unique_id IS NULL
) d
ON (d2.Unique_id=d.Unique_id)
WHEN NOT MATCHED THEN
INSERT(Unique_id, Part_no, Country_code, Action)
VALUES(d.Unique_id, d.Part_no, d.Country_code, 'I')
WHEN MATCHED THEN
UPDATE SET d2.Action = 'U', d2.Country_code = d.Country_code;
-- DELETE WHERE d2.loc='DELETE ME';
Kindly help me with this.
