在SQL Server 2012中使用MERGE插入/更新数据 [英] Using MERGE in SQL Server 2012 to insert/update data
问题描述
我正在使用SQL Server 2012,并且具有两个结构相同的表.如果表2中还不存在新记录,我想将它们从表1插入表2.
I am using SQL Server 2012 and have two tables with identical structure. I want to insert new records from table 1 to table 2 if they don't already exist in table 2.
如果它们已经存在,我想更新表2中的所有现有记录.
If they already exist, I want to update all of the existing records in table 2.
我的表中有大约30列,我想更新所有这些列.
There are some 30 columns in my tables and I want to update all of them.
有人可以帮忙吗?我看了互联网上发布的各种链接,但完全不明白我的陈述应该是什么样子.
Can someone please help with this? I had a look at various links posted over internet, but quite don't understand how my statement should look like.
推荐答案
这真的不那么难....
It's really not that hard....
您需要:
- 用于提供数据的源表(或查询)
- 将其合并到的目标表
- 检查这两个表的条件
- 一条声明,如果找到匹配项(在该条件下)怎么办
- 声明(如果没有找到匹配项的话)怎么办
所以基本上,它类似于:
So basically, it's something like:
-- this is your TARGET table - this is where the data goes into
MERGE dbo.SomeTable AS target
-- this is your SOURCE table where the data comes from
USING dbo.AnotherTable AS source
-- this is the CONDITION they have to "meet" on
ON (target.SomeColumn = source.AnotherColumn)
-- if there's a match, so if that row already exists in the target table,
-- then just UPDATE whatever columns in the existing row you want to update
WHEN MATCHED THEN
UPDATE SET Name = source.Name,
OtherCol = source.SomeCol
-- if there's NO match, that is the row in the SOURCE does *NOT* exist in the TARGET yet,
-- then typically INSERT the new row with whichever columns you're interested in
WHEN NOT MATCHED THEN
INSERT (Col1, Col2, ...., ColN)
VALUES (source.Val1, source.Val2, ...., source.ValN);
这篇关于在SQL Server 2012中使用MERGE插入/更新数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!