SQL Server - 从登台表复制数据

I am using staging tables to perform validation and insert into live.

我正在使用临时表来执行验证并插入实时。

Suppose I have a table PERSONS

假设我有一张桌子PERSONS

TABLE Persons
(
Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
HouseNumber int,
)

and a STAGING TABLE as follows

和STAGING TABLE如下

TABLE Persons_Staging
(
Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
HouseNumber varchar(255),
)

I need to write a procedure to transfer data from the staging table to the live table while ensuring no duplicates are inserted. How can I achieve that?

我需要编写一个过程来将数据从登台表传输到活动表，同时确保不插入重复项。我怎样才能做到这一点？

Thanks in advance

提前致谢

2 个解决方案

#1

Use the MERGE command.

使用MERGE命令。

Something like this:

像这样的东西：

MERGE 
    INTO Persons AS TARGET
    USING Persons_Staging AS SOURCE
    ON TARGET.ID = SOURCE.ID
    --WHEN MATCHED
    --    THEN UPDATE???
    WHEN NOT MATCHED BY TARGET
        THEN INSERT (Id , LastName , FirstName, HouseNumber)
    VALUES (SOURCE.Id , SOURCE.LastName , SOURCE.FirstName, SOURCE.HouseNumber)
    -- WHEN NOT MATCHED BY SOURCE
    --    THEN DELETE???
;

If you want to update existing records you uncomment the UPDATE part and add a suitable update clause. The same with the delete part.

如果要更新现有记录，请取消注释UPDATE部分并添加适当的更新子句。与删除部分相同。

#2

you could use this with a left outer join on both tables to get all the data that isn't the same. That data you then can insert into your column

您可以在两个表上使用左外连接来获取所有不相同的数据。然后，您可以将这些数据插入到列中

INSERT INTO Tab1(front,end,number)
SELECT first,last,nr from tab2 LEFT OUTER JOIN tab1 ON front = first AND last = end AND convert(int,number) = CONVERT(int,nr)
WHERE tab1.ID is null

this could work, on the other hand there are tools made for this kind of stuff

这可能有用，另一方面也有为这种东西制作的工具

#1