question

swarnava2014 avatar image
swarnava2014 asked

How to perform conditional insert using merge statement?

Below is the table structure:

create table source_a (cid varchar(10), fname varchar(50),record_status int)

create table target_a (cid varchar(10), fname varchar(50),record_status int)

insert into source_a values ('C001', 'Sam',0)

insert into source_a values ('C002', 'Roy',0)

insert into source_a values ('C003', 'Tom',3)

I want record from source with record_status = 0 should be inserted into target. How to do it?

Below query is not working:

merge target_a as target using source_a as source on (target.cid=source.cid ) when matched then update set target.fname = source.fname, target.record_status = source.record_status when not matched by target then insert (cid,fname,record_status) values (source.cid,source.fname,source.record_status);

Thanks in advance

sqlsql-server-2008-r2meta-askssc
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.