Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
687 views
in Technique[技术] by (71.8m points)

tsql - Can I use the MERGE statement in SQL Server 2005?

I am using SQL Server 2005 and I wanted to create MERGE statement or concept in single query in SQL Server 2005. Is it possible?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

MERGE was introduced in SQL Server 2008. If you want to use that syntax, you'll need to upgrade.

Otherwise, the typical approach will depend on where the source data is from. If it's just one row and you don't know if you need to update or insert, you'd probably do:

UPDATE ... WHERE key = @key;

IF @@ROWCOUNT = 0
BEGIN
    INSERT ...
END

If your source is a #temp table, table variable, TVP or other table, you can do:

UPDATE dest SET ...
  FROM dbo.destination AS dest
  INNER JOIN dbo.source AS src
  ON dest.key = src.key;

INSERT dbo.destination SELECT ... FROM dbo.source AS src
  WHERE NOT EXISTS (SELECT 1 FROM dbo.destination WHERE key = src.key);

As with MERGE (and as Michael Swart demonstrated here), you will still want to surround any of these methods with proper transactions, error handling and isolation level to behave like a true, single operation. Even a single MERGE statement does not protect you from concurrency.

I've published some other cautions about MERGE in more detail here.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...