27,581
社区成员




--> 生成测试数据表: [t1]
IF OBJECT_ID('[t1]') IS NOT NULL
DROP TABLE [t1]
GO
CREATE TABLE [t1] ([s1] [int],[s2] [int],[s3] [nvarchar](10))
INSERT INTO [t1]
SELECT '1','1','a' UNION ALL
SELECT '2','2','b' UNION ALL
SELECT '3','3','c' UNION ALL
SELECT '2','3','c'
--> 生成测试数据表: [t2]
IF OBJECT_ID('[t2]') IS NOT NULL
DROP TABLE [t2]
GO
CREATE TABLE [t2] ([s1] [int],[s2] [int],[s3] [nvarchar](10))
INSERT INTO [t2]
SELECT '1','1','a' UNION ALL
SELECT '2','2','b' UNION ALL
SELECT '3','3','q' UNION ALL
SELECT '2','3','4'
-->SQL2008查询如下:
MERGE t2 USING t1 ON t1.s1=t2.s1 AND t1.s2=t2.s2
WHEN matched THEN UPDATE SET t2.s3 =t1.s3
WHEN NOT MATCHED THEN INSERT VALUES(t1.s1, t1.s2, t1.s3);
SELECT * FROM t2
/*
s1 s2 s3
----------- ----------- ----------
1 1 a
2 2 b
3 3 c
2 3 c
(4 行受影响)
*/
update table2 set
s3 = table1.s3
from table1
where table1.s1=table2.s1
and table1.s2=table2.s2
insert table2(s1,s2,s3)
select s1,s2,s3
from table1
where not exists (
select 1 from table2
where table1.s1=table2.s1
and table1.s2=table2.s2
)