使用 SQL 合并或更新/插入 [英] Using SQL Merge or UPDATE / INSERT

问题描述

我有一个表 (Customer_Master_File)，需要从转储到文件夹中的平面文件进行更新.我有一个 SSIS 包，它运行以获取平面文件并将它们导入临时表 (temp_Customer_Master_File)

I have a table (Customer_Master_File) that needs to updated from flat files dumped into a folder. I have an SSIS package that runs to pick up the flat files and imports them into a temp table (temp_Customer_Master_File)

我一直无法做到的是:

对于临时表中的每条记录，如果主表中存在Customer_Number，则更新它，如果没有则插入临时表的内容.

for each record in the temp table, if the Customer_Number exists in the Master table, update it, if not insert the contents of the temp table.

我正在更新记录的所有字段，而不是查找单个字段的更改.

I'm updating all fields of the record, not looking for individual field changes.

我尝试了 SQL Merge 功能，但是当源数据中有多个记录时它会出错.

I tried the SQL Merge function but it errors when there is more than one record in the source data.

平面文件包含对客户记录的更改，并且一次可能有多个更改.我只想根据需要插入或更新来处理每条记录.

The flat files contain changes to the customer record, and there could be more than one change at a time. I just want to process each record with inserting or updating as necessary.

我也尝试从 TEMP_TABLE WHERE CUSTOMER_NUMBER NOT IN MASTER_FILE 执行 INSERT INTO MASTER_FILE ，但是当它遇到重复的源行时也会失败并出现 PK 错误.

I also tried doing an INSERT INTO MASTER_FILE FROM TEMP_TABLE WHERE CUSTOMER_NUMBER NOT IN MASTER_FILE but this also fails with a PK error when it hits a duplicate source row.

推荐答案

UPDATE m SET 
  col2 = t.col2, 
  col3 = t.col3 -- etc. - all columns except Customer_Number
FROM dbo.Master_File AS m
INNER JOIN 
(
  SELECT 
    Customer_Number, rn = ROW_NUMBER() OVER
    (
      PARTITION BY Customer_Number ORDER BY [timestamp_column] DESC
    ), col2, col3, ... etc ...
  FROM dbo.Temp_Table
) AS t
ON m.Customer_Number = t.Customer_Number
WHERE t.rn = 1;

INSERT dbo.Master_File(Customer_Number, col2, col3, ...etc...)
  SELECT Customer_Number, col2, col3, ...etc...
  FROM 
  (
    SELECT 
      Customer_Number, rn = ROW_NUMBER() OVER 
      (
        PARTITION BY Customer_Number ORDER BY [timestamp_column DESC
      ),
      col2, col3, ...etc...
    FROM dbo.Temp_Table AS t 
    WHERE NOT EXISTS 
    (
      SELECT 1 FROM dbo.Master_File AS m
      WHERE m.Customer_Number = t.Customer_Number
    )
  ) AS x WHERE rn = 1;

这会处理源表中目标中尚不存在的多行.我对列名做了一个假设，你必须调整.

This takes care of multiple rows in the source table that don't already exist in the destination. I've made an assumption about column names which you'll have to adjust.

MERGE 可能很诱人，但有几个原因让我回避它:

MERGE may be tempting, however there are a few reasons I shy away from it:

1. 语法令人生畏且难以记住...
2. 你没有除非您有意添加特定的锁定提示...
3. MERGE 有许多未解决的错误 可能还有更多尚未发现的......

我最近也在这里发布了一个警告提示.

这篇关于使用 SQL 合并或更新/插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！