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
sql server merge 做insert和update
最新推荐文章于 2024-03-02 10:31:21 发布