sql server merge 做insert和update

wangzhpwang發表於2016-12-21
CREATE TABLE test1 (id INT, f1 VARCHAR(10),f2 VARCHAR(10),f3 VARCHAR(10))
GO
INSERT INTO test1 SELECT 1,1,1,1
INSERT INTO test1 SELECT 1,2,2,2

go

CREATE TABLE test2 (id INT, f5 VARCHAR(10),f6 VARCHAR(10))
GO
SELECT * FROM dbo.test1
GO
SELECT * FROM dbo.test2
GO


CREATE PROC sp_test AS

BEGIN
MERGE INTO test2 T1
USING (SELECT id,f3,f2 FROM test1) T2
ON ( T1.id=T2.id)
WHEN MATCHED THEN
	UPDATE SET T1.f5 = T2.f2,t1.f6=t2.f3
WHEN NOT MATCHED THEN 
	INSERT (id,f5,f6) VALUES(t2.id,t2.f2,t2.f3);
 

END
GO
EXEC sp_test
GO

SELECT * FROM dbo.test1
GO
SELECT * FROM dbo.test2
GO

相關文章