February 20, 2016 at 12:00 am
I have two tables Source and Target (see attached).
if the Badge IDs and Asset Value and Clerk IDs match then no action.
if the Badge IDs match and Asset Value and Clerk ID do not match then do an Update
If the record does not exist in Target then INSERT from SOURCE.
How can I accomplish using Merge?
Thank you in advance.
February 20, 2016 at 8:11 am
kshah82 (2/20/2016)
I have two tables Source and Target (see attached).if the Badge IDs and Asset Value and Clerk IDs match then no action.
if the Badge IDs match and Asset Value and Clerk ID do not match then do an Update
If the record does not exist in Target then INSERT from SOURCE.
How can I accomplish using Merge?
Thank you in advance.
Thank you for providing sample data.
Below is the code you gave us...but where is this Badge ID you speak of?
I assume you mean Badge ID = Employee ID. If so, you have some ambiguities that need to be sorted out.
Take these two rows:
INSERT dbo.Source2(EmployeeID, ClerkID) Values(123456, 'abcd');
INSERT dbo.Target2(EmployeeID, AssetValue) VALUES(123456, 'abcd');
I assume there should be no update to do here, correct?
Now what about these three rows that match on Employee ID but not on Badge ID, considering also that they match the above two rows on Employee ID:
INSERT dbo.Source2(EmployeeID, ClerkID) Values(123456, 'defg');
INSERT dbo.Source2(EmployeeID, ClerkID) Values(123456, 'xyza');
INSERT dbo.Target2(EmployeeID, AssetValue) VALUES(123456, 'aaaa');
What is the desired result? This is what is missing to help complete the picture of what you're trying to do. Based on your sample data, what is the desired result?
As an aside, once your business rules are clear the solution I will suggest will not use MERGE. Instead, it will use an UPDATE followed by an INSERT, both within an explicit transaction, but we will get to that later.
Note, you can surround your code in the message text with these tags to make it show up inline [code="sql"][/code] with syntax highlighting, like this:
USE tempdb;
GO
CREATE TABLE dbo.Target2(EmployeeID int, AssetValue varchar(10));
CREATE TABLE dbo.Source2(EmployeeID int, ClerkID varchar(10));
GO
INSERT dbo.Target2(EmployeeID, AssetValue) VALUES(123456, 'abcd');
INSERT dbo.Target2(EmployeeID, AssetValue) VALUES(123456, 'aaaa');
GO
INSERT dbo.Source2(EmployeeID, ClerkID) Values(123456, 'abcd');
INSERT dbo.Source2(EmployeeID, ClerkID) Values(123456, 'defg');
INSERT dbo.Source2(EmployeeID, ClerkID) Values(123456, 'xyza');
GO
select * from Source2
select * from Target2
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target2 AS T
USING Source2 AS S
ON (T.EmployeeID = S.EmployeeID AND T.AssetValue = S.ClerkID )
WHEN NOT MATCHED BY TARGET
THEN INSERT(EmployeeID, AssetValue) VALUES(S.EmployeeID, S.ClerkID)
WHEN NOT MATCHED BY Source
THEN UPDATE SET T.AssetValue = S.ClerkID
--WHEN NOT MATCHED BY SOURCE
-- THEN DELETE
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO
select * from Source2
select * from Target2
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 20, 2016 at 3:00 pm
Thanks for the response!
Sorry my bad, Badge ID is he Employee ID.
Take these two rows:
INSERT dbo.Source2(EmployeeID, ClerkID) Values(123456, 'abcd');
INSERT dbo.Target2(EmployeeID, AssetValue) VALUES(123456, 'abcd');
yes, no update
INSERT dbo.Target2(EmployeeID, AssetValue) VALUES(123456, 'aaaa'); This record in Target to be updated by:
INSERT dbo.Source2(EmployeeID, ClerkID) Values(123456, 'defg');
This record which does exist in Target to be inserted:
INSERT dbo.Source2(EmployeeID, ClerkID) Values(123456, 'xyza');
I hope this helps.
February 20, 2016 at 10:04 pm
kshah82 (2/20/2016)
Thanks for the response!Sorry my bad, Badge ID is he Employee ID.
Take these two rows:
INSERT dbo.Source2(EmployeeID, ClerkID) Values(123456, 'abcd');
INSERT dbo.Target2(EmployeeID, AssetValue) VALUES(123456, 'abcd');
yes, no update
OK, got it.
INSERT dbo.Target2(EmployeeID, AssetValue) VALUES(123456, 'aaaa'); This record in Target to be updated by:
INSERT dbo.Source2(EmployeeID, ClerkID) Values(123456, 'defg');
This record which does exist in Target to be inserted:
INSERT dbo.Source2(EmployeeID, ClerkID) Values(123456, 'xyza');
I hope this helps.
The logic you presented does not quite hold up. How is the engine supposed to know to update target row from aaaa to defg and not insert a new row, yet know that it should insert a new row with AssetValue xyza? Do you see what I am getting at? You need another column that relates the two tables in order to make a proper decision on when to update and when to insert. At present, with the logic you have shown, the best you can do is insert all rows into target that do not have a matching key of EmployeeID and ClerkID/AssetValue, i.e. no unambiguous updates are possible.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 21, 2016 at 7:34 am
Thanks, I understand the issue but I was thinking if the record exists in both tables and the Clerk ID and Asset Value does not match then do the update. And, if the record exists in source but not in target then do an insert. Is this possible?
February 21, 2016 at 7:48 am
kshah82 (2/21/2016)
Thanks, I understand the issue but I was thinking if the record exists in both tables and the Clerk ID and Asset Value does not match then do the update. And, if the record exists in source but not in target then do an insert. Is this possible?
That logic is possible to implement but in your example you will only ever update rows and will actually try updating the same row in the target table multiple times. MERGE will actually disallow this behavior because it is nonsensical. It is actually one of MERGEs few redeeming qualities.
Long story short, are there any other data points in your tables that will help you match rows across the tables and know, unambiguously, when to insert a new row and when to update and existing row? The idea is that for every row in the source, you should be able to join to 0 or 1 rows in target, never 2 or more. When source joins to 1 row in target you will update the target row. Similarly, when source joins to 0 rows in target you will insert a new row in target.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply