How to copy only modified columns for all rows of table 1 into table 2 in sql server 2014

sqlbulkcopy sql-server sql-server-2012

Question

  1. I have 2 tables table1 and table2.
  2. Initially table1 have some data and table2 is blank table having same column structure(Replica).

  3. I have copied all data from table1 to table2 at first. now after few days ,some columns of few rows get modified in table1 by external application.

  4. Now here is my actual problem starts How to update only those modified columns of table1 into table2 in sql server. I am open for stored procedure, batch job,SSIS or any other way in sql server.
1
0
8/10/2018 10:06:20 AM

Accepted Answer

Ill write this an answer so you can get inspiration to how its done.

The script will insert new records to table2. When rows are matching on your businesskey, but other attributes has changed it updates the row in the second table based upon your businesskey.

MERGE dbo.Client_SCD1 AS DST
USING CarSales.dbo.Client AS SRC
ON (SRC.ID = DST.BusinessKey)
WHEN NOT MATCHED THEN
INSERT (BusinessKey, ClientName, Country, Town, County, Address1, Address2, ClientType, ClientSize)
VALUES (SRC.ID, SRC.ClientName, SRC.Country, SRC.Town, SRC.County, Address1, Address2, ClientType, ClientSize)
WHEN MATCHED 
AND (
 ISNULL(DST.ClientName,'') <> ISNULL(SRC.ClientName,'') 
 OR ISNULL(DST.Country,'') <> ISNULL(SRC.Country,'') 
 OR ISNULL(DST.Town,'') <> ISNULL(SRC.Town,'')
 OR ISNULL(DST.Address1,'') <> ISNULL(SRC.Address1,'')
 OR ISNULL(DST.Address2,'') <> ISNULL(SRC.Address2,'')
 OR ISNULL(DST.ClientType,'') <> ISNULL(SRC.ClientType,'')
 OR ISNULL(DST.ClientSize,'') <> ISNULL(SRC.ClientSize,'')
 )
THEN UPDATE 
SET 
 DST.ClientName = SRC.ClientName 
 ,DST.Country = SRC.Country 
 ,DST.Town = SRC.Town
 ,DST.Address1 = SRC.Address1
 ,DST.Address2 = SRC.Address2
 ,DST.ClientType = SRC.ClientType
 ,DST.ClientSize = SRC.ClientSize
;

Have a look here for SCD type 1 - Im guessing thats what your searching for

0
8/10/2018 10:20:19 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow