November 1, 2016 at 1:20 pm
I have two tables and I have to merge the records.
source:
ID seq name designation company
001 1 aaa Developer YYY
001 2 aaa lead yyy
002 1 mmm consultant bbb
003 1 ppp developer yyy
003 2 ppp lead yyy
003 3 ppp manager yyy
target:
ID seq name designation company
001 1 aaa Developer YYY
001 2 aaa lead yyy
002 1 mmm consultant bbb
003 1 ppp developer yyy
003 2 ppp lead yyy
003 3 ppp manager yyy
I want to write merge to insert/update records. My key columns are ID and seq both.they are composite primary keys. generally it is an update record we get. For example, ID 001 has two records in source . if ID 001 gets another record with sequence 3 , then this record should go as an insert in target.
if my source now has source:
ID seq name designation company
001 1 aaa Developer YYY
001 2 aaa lead yyy
001 3 aaa manager yyy
002 1 mmm consultant bbb
003 1 ppp developer yyy
003 2 ppp lead yyy
003 3 ppp manager yyy
then target should be: target:
ID seq name designation company
001 1 aaa Developer YYY
001 2 aaa manager yyy
001 3 aaa manager yyy
002 1 mmm consultant bbb
003 1 ppp developer yyy
003 2 ppp lead yyy
003 3 ppp manager yyy
I am trying with below merge and it is not working for me.
MERGE target t
using source s
on s.ID=t.ID and s.seq=t.seq
when not matched
then
Insert
(
ID,
seq,
name,
designation,
company
)
Values
(
s.ID,
s.seq,
s.name,
s.designation,
s.company
)
when matched
then
update
set
name=s.name,
designation=s.designation,
company=s.company
;
Can you please let me know where am I going wrong? any help is appreciated
November 1, 2016 at 1:52 pm
Telling us that something is "not working" is most unhelpful. Please elucidate.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 1, 2016 at 3:11 pm
if ID 001 gets another record with sequence 3 , then this record should go as an insert in target.
So far you've only coded for an UPDATE on a match. You have to specify that you want to do an INSERT when NOT MATCHED.
MERGE Production.UnitMeasure AS target
USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
ON (target.UnitMeasureCode = source.UnitMeasureCode)
WHEN MATCHED THEN
UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
INSERT (UnitMeasureCode, Name)
VALUES (source.UnitMeasureCode, source.Name)
See the examples posted here: https://msdn.microsoft.com/en-us/library/bb510625.aspx
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 1, 2016 at 3:33 pm
The Dixie Flatline (11/1/2016)
if ID 001 gets another record with sequence 3 , then this record should go as an insert in target.
So far you've only coded for an UPDATE on a match. You have to specify that you want to do an INSERT when NOT MATCHED.
MERGE Production.UnitMeasure AS target
USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
ON (target.UnitMeasureCode = source.UnitMeasureCode)
WHEN MATCHED THEN
UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
INSERT (UnitMeasureCode, Name)
VALUES (source.UnitMeasureCode, source.Name)
See the examples posted here: https://msdn.microsoft.com/en-us/library/bb510625.aspx
The WHEN NOT MATCHED is there. It's just hard to see, because the OP didn't use the IFCode shortcuts when posting his question.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 2, 2016 at 1:00 pm
My error. I usually put the WHEN MATCHED logic first. Thanks, Drew. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply