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.
| 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 |
|
|
|
|
|
|
Table Sync