Merge statement and parameters

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 statement and parameters

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-03-10 : 06:58:40

Which is better. Both methods seem to work, but is one preferable over the other? (execution plans are identical - but no data in table)

create table Table1(id int, param1 int, param2 int)

declare @id int, @param1 int, @param2 int

MERGE dbo.Table1 As Target
USING ( SELECT @id, @param1, @param2) AS Source (id, param1, param2)
ON (Target.id = Source.id)
WHEN MATCHED THEN
UPDATE SET param1 = Source.param1, param2 = Source.param2
WHEN NOT MATCHED THEN
INSERT (id, param1, param2)
VALUES (Source.id, Source.param1, Source.param2);

MERGE dbo.Table1 As Target
USING ( SELECT @id, @param1, @param2) AS Source (id, param1, param2)
ON (Target.id = Source.id)
WHEN MATCHED THEN
UPDATE SET param1 = @param1, param2 = @param2
WHEN NOT MATCHED THEN
INSERT (id, param1, param2)
VALUES (@id, @param1, @param2);

So use the parameters or the Source table created by the params?

Thanks

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources