代码之家 › 专栏 › 技术社区 › Skitzafreak

将唯一数据插入表中

sql-server sql

-1

Skitzafreak · 技术社区 · 7 年前

我正在使用SQL Server升级数据库。目前,我正在尝试清理一个表,以消除一大堆重复的记录。不过,我的查询似乎无法正常工作。

CREATE TABLE Temp_A
(
    Order_ID INT NOT NULL,
    Job_Number VARCHAR(20) NOT NULL,
    Supplier_Name VARCHAR(50) NOT NULL 
);

BULK INSERT Temp_A
FROM 'This\is\the\file\path.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

CREATE TABLE Temp_B
(
    Order_ID INT NOT NULL,
    Job_Number VARCHAR(20) NOT NULL,
    Supplier_Name VARCHAR(50) NOT NULL 

    CONSTRAINT Temp_Con UNIQUE (Order_ID, Job_Number)
);

INSERT INTO Temp_B
    SELECT Order_ID, Job_Number, Supplier_Name
    FROM Temp_A AS A
    WHERE NOT EXISTS (SELECT 1 
                      FROM Temp_B AS B
                      WHERE B.Order_ID = A.Order_ID
                        AND B.Job_Number = A.Job_Number)

我的代码中不起作用的部分是 INSERT INTO Temp_B 在最后堵住。我所做的是将csv文件中的数据插入 Temp_A 表,然后尝试获取所有唯一的 Order_ID & Part_Number 配对并储存在 Temp_B 表。

我很想进去手动删除这些副本,但有成千上万的记录,所以…是的,这将需要永远。我不知道从这里到哪里去。

编辑:要在收到的错误消息中添加:

违反唯一键约束“temp_con”。无法在对象“dbo.temp”中插入重复键。重复的键值是(3,l154)

4 回复 | 直到 7 年前

EzLo tumao kaixin 7 年前

有一个唯一的by 2列,但源数据有3列。如果有多个行具有相同的 Order_ID 和 Job_Number ?

使用 GROUP BY 具有 MAX() .

INSERT INTO Temp_B (
    Order_ID, 
    Job_Number, 
    Supplier_Name
SELECT 
    Order_ID, 
    Job_Number, 
    Supplier_Name = MAX(Supplier_Name)
FROM 
    Temp_A AS A
WHERE 
    NOT EXISTS (
        SELECT 
            'not yet in Temp_B' 
        FROM 
            Temp_B AS B
        WHERE 
            B.Order_ID = A.Order_ID AND 
            B.Job_Number = A.Job_Number)
GROUP BY
    A.Order_ID,
    A.Job_Number

使用 ROW_NUMBER() .

;WITH MissingRanked AS
(
    SELECT 
        Order_ID, 
        Job_Number, 
        Supplier_Name,
        Ranking = ROW_NUMBER() OVER (
            PARTITION BY 
                A.Order_ID, 
                Job_Number 
            ORDER BY 
                (SELECT NULL)) -- Your ordering criteria here
    FROM 
        Temp_A AS A
    WHERE 
        NOT EXISTS (
            SELECT 
                'not yet in Temp_B' 
            FROM 
                Temp_B AS B
            WHERE 
                B.Order_ID = A.Order_ID AND 
                B.Job_Number = A.Job_Number)
)
INSERT INTO Temp_B (
    Order_ID, 
    Job_Number, 
    Supplier_Name
SELECT
    Order_ID, 
    Job_Number, 
    Supplier_Name
FROM
    MissingRanked AS M
WHERE
    M.Ranking = 1

sniperd Ali Ahmed 7 年前

我将尝试使插入的内容与group by唯一,而不是如下所示:

INSERT INTO Temp_B
SELECT Order_ID, Job_Number, Supplier_Name
FROM Temp_A AS A
GROUP BY A.Order_ID, A.Job_Number, A.Supplier_Name

我没有数据可供测试,但我认为这是可行的。你的问题是 Order_ID & Part_Number 不过,我猜是O型的,但你知道的。不过,这是我要走的方向。你也可以用 DISTINCT 但我喜欢 GROUP BY

kutschkem 7 年前

您的方法不起作用,因为子选择将记录视为插入之前的记录—也就是说,它将看到一个空表。

相反,您需要的是distinct关键字。

INSERT INTO Temp_B
SELECT DISTINCT Order_ID, Job_Number, Supplier_Name
FROM Temp_A

Aura 7 年前

可以在插入查询中添加DISTINCT关键字:

INSERT INTO Temp_B
SELECT DISTINCT Order_ID, Job_Number, Supplier_Name
FROM Temp_A AS A
WHERE NOT EXISTS (
SELECT 1 FROM Temp_B AS B
WHERE B.Order_ID = A.Order_ID
AND B.Job_Number = A.Job_Number);