时间戳

首页 > 解决方案 > SQL Server 合并

sql-server - SQL Server 合并

问题描述

我正在从具有 accountId 作为行标识符的平面文件上传 stagingTableB，但它不是一个 int（我希望行标识符是一个 int），所以我在想我们是否可以添加一个唯一的行标识符（或主要键）进入我们的目标表而不复制记录（使用合并和更新）。下面是我的 Merge，它似乎可以工作，但是当我执行包时它会不断添加记录）。请问有什么帮助吗？这是我在 SQL Server 中创建目标表的方式：

Create TABLE TableA(
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[Account ID] [varchar](50) NULL,
[CustomerName] [varchar](50) NULL,
[Address] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [varchar](50) NULL,
[Zip] [varchar](50) NULL,
[Sales Rep  ] [varchar](50) NULL,
[Group Code] [varchar](50) NULL,
[Credit Limit] [varchar](50) NULL

) ON [PRIMARY]

这是暂存表的样子：

Create TABLE StagingTableB(
[Account ID] [varchar](50) NULL,
[CustomerName] [varchar](50) NULL,
[Address] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [varchar](50) NULL,
[Zip] [varchar](50) NULL,
[Sales Rep  ] [varchar](50) NULL,
[Group Code] [varchar](50) NULL,
[Credit Limit] [varchar](50) NULL

 )

这是合并查询

MERGE INTO TableA as TARGET
USING StagingTableB as SOURCE
ON
TARGET.[Account ID] = SOURCE.[Account ID]

WHEN MATCHED THEN
UPDATE SET   
   TARGET.[Account ID] = SOURCE.[Account ID]
  ,TARGET.[CustomerName] = SOURCE.[CustomerName]
  ,TARGET.[Address] = SOURCE.[Address]
  ,TARGET.[City] = SOURCE.[City]
  ,TARGET.[State] = SOURCE.[State]
  ,TARGET.[Zip] = SOURCE.[Zip]
  ,TARGET.[Sales Rep] = SOURCE.[Sales Rep]
  ,TARGET.[Group Code] = SOURCE.[Group Code]
  ,TARGET.[Credit Limit] = SOURCE.[Credit Limit]
WHEN NOT MATCHED THEN
INSERT ([Account ID], [CustomerName], [Address], [City], [State], [Zip], [Sales Rep], [Group Code], [Credit Limit])
 VALUES
       (SOURCE.[Account ID], SOURCE.[CustomerName], SOURCE.[Address], 
 SOURCE.[City], SOURCE.[State], SOURCE.[Zip], SOURCE.[Sales Rep], SOURCE.[Group Code], SOURCE.[Credit Limit]);

标签： sql-server

解决方案

触发器在语句级别工作。因此，当使用“合并”时，会为合并语句中涉及的所有 DML 操作触发触发器。相反，将您的代码转换为以下代码以避免来自触发器的不必要插入。

 UPDATE Res1
 SET 
 [CustomerName] = Res2.[CustomerName]
 ,[Address] = Res2.[Address]
 ,[City] = Res2.[City]
 ,[State] = Res2.[State]
 ,[Zip] = Res2.[Zip]
 ,[Sales Rep] = Res2.[Sales Rep]
 ,[Group Code] = Res2.[Group Code]
 ,[Credit Limit] = Res2.[Credit Limit]
 FROM TableA Res1
 JOIN StagingTableB Res2 ON Res2.[Account ID] = Res1.[Account ID]

INSERT INTO TableA([Account ID], [CustomerName], [Address], [City], [State], [Zip], [Sales Rep], [Group Code], [Credit Limit])
SELECT 
 Res1.[Account ID]
,Res1.[CustomerName]
,Res1.[Address]
,Res1.[City]
,Res1.[State]
,Res1.[Zip]
,Res1.[Sales Rep]
,Res1.[Group Code]
,Res1.[Credit Limit]
FROM StagingTableB Res1
LEFT JOIN TableA Res2 ON Res2.[Account ID] = Res1.[Account ID]
WHERE Res2.[Account ID] IS NULL

推荐阅读