CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Diane Baker
Diane Baker

Reputation: 11

Transferring records between two tables with no duplicates

I have data in a table named CENSUS (columns: APN, ATTRIB_VALUE, ATTNAME) that needs to be transferred to a table named PARCEL_ATTR (columns: L1_PARCEL_NBR, L1_ATTRIB_VALUE, L1_ATTRIB_NAME).

PARCEL_ATTR's primary key is two columns L1_PARCEL_NBR and L1_ATTRIB_NAME.

I have tried to use the following SQL statement to insert records from CENSUS into PARCEL_ATTR without success:

INSERT INTO dbo.PARCEL_ATTR (L1_PARCEL_NBR, L1_ATTRIB_VALUE, L1_ATTRIB_NAME) 
    SELECT 
        APN, ATTRIB_VALUE, ATTNAME 
    FROM 
        dbo.CENSUS 
    WHERE 
        NOT EXISTS (SELECT 1 
                    FROM PARCEL_ATTR 
                    WHERE CENSUS.APN = PARCEL_ATTR.L1_PARCEL_NBR 
                      AND CENSUS.ATTNAME = PARCEL_ATTR.L1_ATTRIB_NAME);

I get this error from SQL Server 2008 R2 every time:

Violation of PRIMARY KEY constraint 'PKparcel_attr'. Cannot insert duplicate key in object 'dbo.Parcel_Attr'. The duplicate key value is (002-001-021, Census ).

Can anyone help me understand what I'm doing wrong?

Upvotes: 1

Views: 80

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

You are only filtering the records which is already present in the PARCEL_ATTR table. But you missed to remove the duplicates in CENSUS table.

Use Window Function to remove duplicates from CENSUS table. Try this.

INSERT INTO dbo.PARCEL_ATTR
            (L1_PARCEL_NBR,
             L1_ATTRIB_VALUE,
             L1_ATTRIB_NAME)
SELECT APN,
       ATTRIB_VALUE,
       ATTNAME
FROM   (SELECT Row_number()OVER (partition BY APN, ATTNAME ORDER BY ATTRIB_VALUE) rn,
               APN,
               ATTRIB_VALUE,
               ATTNAME
        FROM   dbo.CENSUS
        WHERE  NOT EXISTS (SELECT 1
                           FROM   PARCEL_ATTR
                           WHERE  CENSUS.APN = PARCEL_ATTR.L1_PARCEL_NBR
                                  AND CENSUS.ATTNAME = PARCEL_ATTR.L1_ATTRIB_NAME))A
WHERE  rn = 1;

Upvotes: 1

user2941651
user2941651

Reputation:

Please try the following:

INSERT INTO dbo.PARCEL_ATTR (L1_PARCEL_NBR, L1_ATTRIB_VALUE, L1_ATTRIB_NAME) 
SELECT CENSUS.APN, CENSUS.ATTRIB_VALUE, CENSUS.ATTNAME 
FROM dbo.CENSUS 
     LEFT OUTER JOIN PARCEL_ATTR AS A 
     ON CENSUS.APN=A.L1_PARCEL_NBR AND CENSUS.ATTNAME=A.L1_ATTRIB_NAME)
WHERE
     A.L1_PARCEL_NBR IS NULL

I hope it helps some way.

Upvotes: 0

Related Questions