如何防止将重复数据插入到值为多个的 SQL Server 表中

Question

我声明了一些需要插入到SQL中的数据，我应该怎么做才能检查SQL中的数据以防止相同的数据插入到表中？

123(AA) 已上传，123(BB) 是新受邀者。 在这种情况下，服务器应该只上传 123(BB)。

declare @inviteename VARCHAR(500) = 'aa,bb' ,@inviteephoneno VARCHAR(500) = '123,45',@Code varchar(10)='123', @CustomerID VARCHAR(50) = 10  
DECLARE @tbl TABLE (
inviteename VARCHAR(100), 
inviteephoneno VARCHAR(100)
);
INSERT INTO @tbl ( inviteename, inviteephoneno) select  @inviteename, @inviteephoneno;
DECLARE @temp TABLE (
inviteename VARCHAR(100), 
inviteephoneno VARCHAR(100),
CustomerID VARCHAR(100)
);
;WITH cte1 AS
(
SELECT  value AS inviteename , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
FROM @tbl CROSS APPLY STRING_SPLIT(inviteename, ',')
), cte2 AS
(
SELECT  value AS inviteephoneno , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
FROM @tbl CROSS APPLY STRING_SPLIT(inviteephoneno, ',')
)
INSERT INTO @temp ( inviteename, inviteephoneno,CustomerID)
SELECT cte1.inviteename, cte2.inviteephoneno,@CustomerID   FROM cte1 INNER JOIN cte2 ON cte2.seq = cte1.seq; 
if not  exists (select 1 from test a where a.inviteename= (select InviteeName from @temp a where a.CustomerID=@CustomerID)
and a.InviteeMobileNumber = (select InviteeMobileNumber from @temp a where a.CustomerID=@CustomerID))    
begin
INSERT dbo.test (InviteeName ,InviteeMobileNumber ) 
SELECT a.inviteename, a.inviteephoneno  FROM @temp a 
end

测试台

create table test ( 
id int identity(1,1)
, inviteename VARCHAR(100)
, inviteephoneno VARCHAR(100) );

Answer 1

您可以使用合并

create table test ( 
id int identity(1,1)
, inviteename VARCHAR(100)
, InviteeMobileNumber VARCHAR(100) );
GO

declare @inviteename VARCHAR(500) = 'aa,bb' 
,@inviteephoneno VARCHAR(500) = '123
,45',@Code varchar(10)='123'
, @CustomerID VARCHAR(50) = 10  

DECLARE @tbl TABLE (
inviteename VARCHAR(100), 
inviteephoneno VARCHAR(100)
);

INSERT INTO @tbl ( inviteename, inviteephoneno) select  @inviteename, @inviteephoneno;

DECLARE @temp TABLE (
inviteename VARCHAR(100), 
inviteephoneno VARCHAR(100),
CustomerID VARCHAR(100)
);

;WITH cte1 AS
(
SELECT  value AS inviteename , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
FROM @tbl CROSS APPLY STRING_SPLIT(inviteename, ',')
), cte2 AS
(
SELECT  value AS inviteephoneno , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
FROM @tbl CROSS APPLY STRING_SPLIT(inviteephoneno, ',')
)

INSERT INTO @temp ( inviteename, inviteephoneno,CustomerID)
SELECT cte1.inviteename, cte2.inviteephoneno,@CustomerID   FROM cte1 INNER JOIN cte2 ON cte2.seq = cte1.seq; 
MERGE dbo.test AS T
USING @temp AS S
ON (T.inviteename = S.inviteename AND T.InviteeMobileNumber = S.inviteephoneno) 
WHEN NOT MATCHED BY TARGET  
THEN INSERT (InviteeName ,InviteeMobileNumber) VALUES(S.inviteename, S.inviteephoneno)
OUTPUT $action, Inserted.*;

GO

$action | ID | 受邀者姓名 |受邀手机号码 :------ |-: |:---------- |:------------------ 插入 | 1 |机管局 |123<>            插入 | 2 |BB |45

SELECT * FROM test
GO

ID | 受邀者姓名 |受邀手机号码 -: |:---------- |:------------------  1 |机管局 |123<>             2 |BB |45

db<>在这里小提琴