SQL Server:主键冲突

Question

首先，我想为您即将经历的混乱代码道歉，但这对我的观点很重要。我在一定程度上简化了它，但这里是我正在使用的代码：

create table [TableExample](
ID varchar(50) primary key not null,
);
with CTE as (
select case when ltrim(rtrim(S1.ID)) is null
then convert(varchar(50),newid())
else ltrim(rtrim(S1.ID))
end as 'ID',
row_number()over(partition by ltrim(rtrim(S1.ID)) order by newid()) as 'duplicates' 
from [DB2].[dbo].[Source1] as S1
full outer join [DB2].[dbo].[Source2] as S2
on S2.[ID]=S1.[ID]
full outer join [DB2].[dbo].[Source3] as S3
on S3.[ID]=S1.[ID])
insert into [dbo].[TableExample] (
[ID]
)
select  distinct case 
when [duplicates] > 1
then convert(varchar(50),newid())
else CTE.[ID]
end as 'ID'
from [DB2].[dbo].[Source1] as S1
full outer join [DB2].[dbo].[Source2] as S2
on ltrim(rtrim(S2.ID))=ltrim(rtrim(S1.ID))
full outer join [DB2].[dbo].[Source3] as S3
on ltrim(rtrim(S3.ID))=ltrim(rtrim(S1.ID))
full outer join CTE on CTE.ID=ltrim(rtrim(S1.ID))
where (S2.ID is not null or S3.ID is not null or S1.ID is not null)
and [duplicates] = 1

正如您所看到的，我正在运行多个非常冗余的检查，以确保主键字段[ID]没有收到任何重复项。我选择distinct，使用CTE标记任何重复的密钥，然后拒绝它们，最后如果仍然有通过，我将重复的密钥更改为

convert(varchar(50),newid())

然而，每次我运行这个程序时，我都会遇到一个重复的密钥错误。

如果你想知道，是的，我每次都会放下桌子，只是为了确保没有任何可能导致错误的东西被保留下来。

有超过100000行的数据需要输入，我对此束手无策。任何建议都将不胜感激！

Answer 1

您似乎正在尝试合并Source1、Source2和Source3中的ID，对它们进行修剪，并查找重复项。如果有重复的行，请保留其中一行，并用新的uniqueidentifier替换其他行。这恰恰实现了这一点：

CREATE TABLE TableExample ( ID VARCHAR(50) PRIMARY KEY NOT NULL)
CREATE TABLE Source1 ( ID VARCHAR(50) PRIMARY KEY NOT NULL)
CREATE TABLE Source2 ( ID VARCHAR(50) PRIMARY KEY NOT NULL)
CREATE TABLE Source3 ( ID VARCHAR(50) PRIMARY KEY NOT NULL)
INSERT INTO Source1 VALUES ('6C0240E7-847B-41F3-9DCA-D2EA54FB0E96'), (' 6C0240E7-847B-41F3-9DCA-D2EA54FB0E96 '), ('007171B1-10DB-4DD2-832F-026C968AC19A'), ('A28976BC-443F-44E6-9AED-B41DAFBBB651')
INSERT INTO Source2 VALUES ('6C0240E7-847B-41F3-9DCA-D2EA54FB0E96 '), ('007171B1-10DB-4DD2-832F-026C968AC19A'), ('80373FA7-5A61-41AB-B7DA-1F807A0E442B'), ('A38C6C43-A2B1-4C5B-8699-601054FB7968')
INSERT INTO Source3 VALUES ('  6C0240E7-847B-41F3-9DCA-D2EA54FB0E96 '), ('1A366E5C-0B6C-4B3D-B3E4-126035D641F1'), ('1183D160-AF00-43C1-8EA1-E953B51918E4'), (' 80373FA7-5A61-41AB-B7DA-1F807A0E442B ')
;WITH CTE AS
(
SELECT RTRIM(LTRIM(ID)) ID
FROM Source1 
UNION ALL
SELECT RTRIM(LTRIM(ID)) ID
FROM Source2
UNION ALL
SELECT RTRIM(LTRIM(ID)) ID
FROM Source3
),
CTE2 AS
(
SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY NEWID()) AS RN
FROM CTE
)
INSERT INTO TableExample (ID)
SELECT CASE WHEN RN > 1 THEN CAST(NEWID() AS VARCHAR(50)) ELSE ID END AS ID
FROM CTE2
SELECT * FROM TableExample

输出：

ID
007171B1-10DB-4DD2-832F-026C968AC19A
0841CEF6-B70E-442F-90CD-B79C235107F9
0AE6C8EC-31CF-49EA-B992-09E0CDC32B61
1183D160-AF00-43C1-8EA1-E953B51918E4
16EE8FD4-E5C9-480E-98DB-1410DCB0CFF5
1A366E5C-0B6C-4B3D-B3E4-126035D641F1
2C5F8AE0-CE70-406C-96C4-FE5144C16634
6C0240E7-847B-41F3-9DCA-D2EA54FB0E96
80373FA7-5A61-41AB-B7DA-1F807A0E442B
A28976BC-443F-44E6-9AED-B41DAFBBB651
A38C6C43-A2B1-4C5B-8699-601054FB7968
CB426799-E85E-4D13-AC6A-16ACC571333A