Merge with more then one insert statement

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Merge with more then one insert statement

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-16 : 10:41:03

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)

Note : id in table1 is identity column

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.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-16 : 11:12:01

You might try UNPIVOT of Table2 into a temp table then do the merge

djj

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-16 : 11:13:02

can u please show some sample how to do this?

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-17 : 10:10:33

Finally i am done with



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);

hope this helps to others as well.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-17 : 11:45:12

[code]MERGE dbo.Table1 AS tgt
USING (
SELECT f.Name
FROM dbo.Table2 AS t
CROSS APPLY (
VALUES (t.Name1),
(t.Name2),
(t.Name3)
) AS f(Name)
) AS src ON src.Name = tgt.Name
WHEN NOT MATCHED BY TARGET
THEN INSERT (
Name
)
VALUES (
src.Name
);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources