Table Sync - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Table Sync

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2015-04-30 : 01:52:46

I have two test databases DB1 and DB2
Both have tables ClientStatus with a field called status and ledgerKey

DB1 is updated by our customers

What I would like to do is keep the status field in DB2 in sync with DB1 if the ledgerkey exists in DB2.ClientStatus
The field values for DB1 are ‘Accepted’ , ‘Unsubmitted’ , ‘Submitted’

IF the ledgerkey exists in DB2 and the DB2.ClientStatus <> DB1.ClientStatus then
Select DB1.LedgerKey , Db1.ClientStatus

This query is to be fed into a SSIS workflow for CRM
Any assistance would be most helpful

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-30 : 02:52:09

You can use a MERGE stmt to do this sync.
Here is a sample of it


USE DB2;
GO
;MERGE INTO DB2.dbo.ClientStatus AS T
	USING DB1.dbo.ClientStatus AS S
	ON T.ledgerkey = S.ledgerkey

WHEN MATCHED AND T.[status] <> S.[status]
	THEN UPDATE 
			SET T.[status] = S.[status]
--OUTPUT $ACTION ,Inserted.*,Deleted.*
;

sabinWeb MCP

Pete_N
Posting Yak Master

181 Posts

Posted - 2015-04-30 : 04:08:29

quote:
Originally posted by stepson

You can use a MERGE stmt to do this sync.
Here is a sample of it

USE DB2;
GO
;MERGE INTO DB2.dbo.ClientStatus AS T
USING DB1.dbo.ClientStatus AS S
ON T.ledgerkey = S.ledgerkey

WHEN MATCHED AND T.[status] <> S.[status]
THEN UPDATE
SET T.[status] = S.[status]
--OUTPUT $ACTION ,Inserted.*,Deleted.*
;

sabinWeb MCP

HI,

Thank you for the info, it has been useful in another project, however for this project I need to generate a select Query of the ledgerRef and ClientStatus from DB1 that do not match DB2 as this will form the datasource for a SSIS to updata a CRM system

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-30 : 04:18:21

[code]
SELECT A.[ledgerKey],A.[status]
FROM DB1.dbo.ClientStatus AS A
INNER JOIN DB2.dbo.ClientStatus AS B
ON A.ledgerkey = B.ledgerkey
WHERE A.[status] <> B.[status]
[code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources