适用于： 
SQL Server Azure SQL 数据库 Azure SQL 托管实例
本节中的 Transact-SQL 代码示例演示如何在机编译模块中模拟 T-SQL MERGE 语句。 此示例使用具有标识列的表变量、循环访问表变量中的行，如果条件匹配，则对每行执行更新操作，如果条件不匹配则执行插入操作。
以下是你希望在本机过程中受到支持且代码示例模仿的 T-SQL MERGE 语句。
MERGE INTO dbo.Table1 t
USING @tvp v
ON t.Column1 = v.c1
WHEN MATCHED THEN
UPDATE SET Column2 = v.c2
WHEN NOT MATCHED THEN
INSERT (Column1, Column2) VALUES (v.c1, v.c2);
以下是实现解决方法和模拟 MERGE 的 T-SQL。
DROP PROCEDURE IF EXISTS dbo.usp_merge1;
go
DROP TYPE IF EXISTS dbo.Type1;
go
DROP TABLE IF EXISTS dbo.Table1;
go
-----------------------------
-- target table and table type used for the workaround
-----------------------------
CREATE TABLE dbo.Table1
(
Column1 INT NOT NULL PRIMARY KEY NONCLUSTERED,
Column2 INT NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON);
go
CREATE TYPE dbo.Type1 AS TABLE
(
c1 INT NOT NULL,
c2 INT NOT NULL,
RowID INT NOT NULL IDENTITY(1,1),
INDEX ix_RowID HASH (RowID) WITH (BUCKET_COUNT=1024)
)
WITH (MEMORY_OPTIMIZED = ON);
go
-----------------------------
-- stored procedure implementing the workaround
-----------------------------
CREATE PROCEDURE dbo.usp_merge1
@tvp1 dbo.Type1 READONLY
WITH
NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'us_english')
DECLARE @i INT = 1, @c1 INT, @c2 INT;
WHILE @i > 0
BEGIN
SELECT @c1 = c1, @c2 = c2
FROM @tvp1
WHERE RowID = @i;
--test whether the row exists in the TVP; if not, we end the loop
IF @@ROWCOUNT=0
SET @i = 0
ELSE
BEGIN
-- try the update
UPDATE dbo.Table1
SET Column2 = @c2
WHERE Column1 = @c1;
-- if there was no row to update, we insert
IF @@ROWCOUNT=0
INSERT INTO dbo.Table1 (Column1, Column2)
VALUES (@c1, @c2);
SET @i += 1
END
END
END
go
-----------------------------
-- test to validate the functionality
-----------------------------
INSERT dbo.Table1 VALUES (1,2);
go
SELECT N'Before-MERGE' AS [Before-MERGE], Column1, Column2
FROM dbo.Table1;
go
DECLARE @tvp1 dbo.Type1;
INSERT @tvp1 (c1, c2) VALUES (1,33), (2,4);
EXECUTE dbo.usp_merge1 @tvp1;
go
SELECT N'After--MERGE' AS [After--MERGE], Column1, Column2
FROM dbo.Table1;
go
/**** Actual output:
Before-MERGE Column1 Column2
Before-MERGE 1 2
After--MERGE Column1 Column2
After--MERGE 1 33
After--MERGE 2 4
****/