Merge with more then one insert statement

Question

Post reply

Merge with more then one insert statement

born2achieve

Hall of Fame

Points: 3182
More actions
September 16, 2013 at 8:43 am

#283211

Table1 :
id, Name
Table2:
id, name1,name2,name3,name4,name5
MERGE Table1 t1
USING table2 t2
ON t1.id = t2.id
WHEN NOT MATCHED THEN
INSERT(name)
values(t2.name1)
values(t2.name2)
values(t2.name3)
values(t2.name4)
values(t2.name5)
as like this i have 16 names in my table(t2.name1 ..... t2.name16)
is it possible to do? can we write multiple insert statement in merge clause? if not would like to know the best way to to that.
Note : id in table1 is identity column

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

bugg SSCertifiable Points: 7567 More actions · Answer 1

I think you'll have to change the name columns (name1, name 2 etc) in the SOURCE to rows

So your SOURCE would be

using (

SELECT t2.id,t2.name1 as name FROM table2

UNION ALL

SELECT t2.id,t2.name2 as name FROM table2

UNION ALL

SELECT t2.id,t2.name3 as name FROM table2

UNION ALL

SELECT t2.id,t2.name4 as name FROM table2

...etc

)

born2achieve Hall of Fame Points: 3182 More actions · Answer 2

my ultimate question is can we have multiple insert statements inside merge clause? if yes can you please show some sample how to achieve this requirement

your example showing how to use the Union all which i know. also as i said no need to worry about table id which is identity column. so just i am going to insert name from table to column's

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 3

born2achieve (9/16/2013)
my ultimate question is can we have multiple insert statements inside merge clause? if yes can you please show some sample how to achieve this requirement
your example showing how to use the Union all which i know. also as i said no need to worry about table id which is identity column. so just i am going to insert name from table to column's

I don't believe it is possible to use multiple insert statements in the MERGE - but this will solve what you originally asked about:

MERGE table1 AS target

USING (select name1 from table2

union all

select name2 from table2

union all

select name3 from table2) AS source (name)

ON target.name = source.name

WHEN NOT MATCHED THEN

INSERT (name)

VALUES (source.name);

born2achieve Hall of Fame Points: 3182 More actions · Answer 4

born2achieve

Hall of Fame

Points: 3182

September 16, 2013 at 10:44 am

#1650531

Well, Thanks for your response and useful query.

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 5

born2achieve (9/16/2013)
Well, Thanks for your response and useful query.

Full credit to other poster (bugg) who really helped to solve it with the UNION ALL portion.

bugg SSCertifiable Points: 7567 More actions · Answer 6

batgirl (9/16/2013)
born2achieve (9/16/2013)
Well, Thanks for your response and useful query.
Full credit to other poster (bugg) who really helped to solve it with the UNION ALL portion.

Thanks batgirl 😉

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 7

batgirl (9/16/2013)
born2achieve (9/16/2013)
my ultimate question is can we have multiple insert statements inside merge clause? if yes can you please show some sample how to achieve this requirement
your example showing how to use the Union all which i know. also as i said no need to worry about table id which is identity column. so just i am going to insert name from table to column's
I don't believe it is possible to use multiple insert statements in the MERGE - but this will solve what you originally asked about:
MERGE table1 AS target
USING (select name1 from table2
union all
select name2 from table2
union all
select name3 from table2) AS source (name)
ON target.name = source.name
WHEN NOT MATCHED THEN
INSERT (name)
VALUES (source.name);

You can also use CROSS APPLY instead of multiple UNION ALLs

MERGE table1 AS target

USING (SELECT name

FROM table2

CROSS APPLY(VALUES(name1),(name2),(name3)) ca(name)) AS source (name)

ON target.name = source.name

WHEN NOT MATCHED THEN

INSERT (name)

VALUES (source.name);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

born2achieve Hall of Fame Points: 3182 More actions · Answer 8

born2achieve

Hall of Fame

Points: 3182

September 17, 2013 at 8:09 am

#1650802

Hi Mark,

Thanks a lot for the cross Apply option.