Insert where record doesn't already exist

Neal · February 4th, 2008, 08:47 AM

Hi,

I'm appending records to a table but do NOT wish to include records where the exact record already exists.

I DO wish to append records where the record is:
1. NEW
2. ALTERED - Since it was last uploaded (where altered at source)

The source table is dbo.CONTACTS
and the target table is KW.CONTACTS

I have successfully written the code where I have 1 column in the Primary key, but this table has 4 columns in the Primary key.

Please can you help?

The Primary Key is:
KW.CCO_COMPANY
KW.CCO_TYPE
KW.CCO_CODE
KW.CCO_NAME

Here is my present code:

Code:

use  Zeus_Instance_1_Source_database
go
--2nd + time Import
INSERT INTO KW.CONTACTS
(
CCO_COMPANY, 
CCO_TYPE, 
CCO_CODE, 
CCO_NAME, 
CCO_INITIALS, 
CCO_ALT_CODE, 
CCO_FREE_NAME, 
CCO_DESCRIPTION, 
CCO_PHONE_0, 
CCO_PHONE_1, 
CCO_PHONE_2, 
CCO_PHONE_TEXT_0, 
CCO_PHONE_TEXT_1, 
CCO_PHONE_TEXT_2, 
CCO_FAX, 
CCO_EMAIL, 
FILL
)
SELECT 
RC.CCO_COMPANY, 
RC.CCO_TYPE, 
RC.CCO_CODE, 
RC.CCO_NAME, 
RC.CCO_INITIALS, 
RC.CCO_ALT_CODE, 
RC.CCO_FREE_NAME, 
RC.CCO_DESCRIPTION, 
RC.CCO_PHONE_0, 
RC.CCO_PHONE_1, 
RC.CCO_PHONE_2, 
RC.CCO_PHONE_TEXT_0, 
RC.CCO_PHONE_TEXT_1, 
RC.CCO_PHONE_TEXT_2, 
RC.CCO_FAX, 
RC.CCO_EMAIL, 
RC.FILL
FROM dbo.CONTACTS RC
    LEFT OUTER JOIN KW.CONTACTS KC ON
        KC.CCO_COMPANY = RC.CCO_COMPANY AND
        KC.CCO_TYPE = RC.CCO_TYPE AND
        KC.CCO_CODE = RC.CCO_CODE AND
        KC.CCO_NAME = RC.CCO_NAME
        WHERE
            (--KC.CCO_COMPANY <> RC.CCO_COMPANY OR 
            --KC.CCO_TYPE <> RC.CCO_TYPE OR 
            --KC.CCO_CODE <> RC.CCO_CODE OR 
            --KC.CCO_NAME <> RC.CCO_NAME OR 
            KC.CCO_INITIALS <> RC.CCO_INITIALS OR 
            KC.CCO_ALT_CODE <> RC.CCO_ALT_CODE OR 
            KC.CCO_FREE_NAME <> RC.CCO_FREE_NAME OR 
            KC.CCO_DESCRIPTION <> RC.CCO_DESCRIPTION OR 
            KC.CCO_PHONE_0 <> RC.CCO_PHONE_0 OR 
            KC.CCO_PHONE_1 <> RC.CCO_PHONE_1 OR 
            KC.CCO_PHONE_2 <> RC.CCO_PHONE_2 OR 
            KC.CCO_PHONE_TEXT_0 <> RC.CCO_PHONE_TEXT_0 OR 
            KC.CCO_PHONE_TEXT_1 <> RC.CCO_PHONE_TEXT_1 OR 
            KC.CCO_PHONE_TEXT_2 <> RC.CCO_PHONE_TEXT_2 OR 
            KC.CCO_FAX <> RC.CCO_FAX OR 
            KC.CCO_EMAIL <> RC.CCO_EMAIL OR 
            KC.FILL <> RC.FILL) OR                
            (KC.CCO_COMPANY IS NULL AND
            KC.CCO_TYPE IS NULL AND
            KC.CCO_CODE IS NULL AND
            KC.CCO_NAME IS NULL);

Error Message is:

Code:

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK_CONTACTS'. Cannot insert duplicate key in object 'KW.CONTACTS'.
The statement has been terminated.

Thank you in advance,

Neal

A Northern Soul

urtrivedi · February 4th, 2008, 09:00 AM

If data is changed that does't mean you can insert a same pk again
so you have to execure two queries

first insert new rows
update changed rows

--INSERT QUERY
INSERT INTO KW.CONTACTS (COL1,COL2)
SELECT COL1 COL2 FROM dbo.contacts
where cast(dbo.cco_company as varchar)+','+ cast(dbo.cco_type.as varchar)+','+cast(dbo.cco_code as varchar)+','+cast(dbo.cco_name as varchar)
NOT IN (SELECT cast(KW.cco_company as varchar)+','+ cast(KW.cco_type.as varchar)+','+cast(KW.cco_code as varchar)+','+cast(KW.cco_name as varchar) FROM KW.CONTACTS )

NOW you have every new row after executing above query
You have already written update query so execute that,
means update rows ,using pk where values are changed

urt

Help yourself by helping someone.

joefawcett · February 4th, 2008, 09:28 AM

First update by joining the two contacts tables on the composite primary key and updating any matching rows:

Code:

UPDATE KW.Contacts
  SET 
      CCO_INITIALS = S.CCO_INITIALS, 
      CCO_ALT_CODE = S.CCO_ALT_CODE, 
      CCO_FREE_NAME = S.CCO_FREE_NAME
      /*
        Other columns
      */
 FROM
  KW.Contacts T INNER JOIN DBO.Contacts S
  ON  T.CCO_COMPANY = S.CCO_COMPANY
  AND T.CCO_TYPE = S.CCO_TYPE
  AND T.CCO_CODE = S.CCO_CODE
  AND T.CCO_NAME = S.CCO_NAME;

Then use a left join to find all the new rows for the insert:

Code:

INSERT KW.Contacts
  (
    CCO_COMPANY,
    CCO_TYPE,
    CCO_CODE,
    CCO_NAME,
    CCO_INITIALS, 
    CCO_ALT_CODE, 
    CCO_FREE_NAME
    /*
      Other columns
    */
  )
  FROM
  DBO.Contacts S LEFT OUTER JOIN KW.Contacts T
  ON  T.CCO_COMPANY = S.CCO_COMPANY
  AND T.CCO_TYPE = S.CCO_TYPE
  AND T.CCO_CODE = S.CCO_CODE
  AND T.CCO_NAME = S.CCO_NAME
  WHERE 
    T.CCO_COMPANY IS NULL
    AND T.CCO_TYPE IS NULL
    AND T.CCO_CODE IS NULL
    AND T.CCO_NAME IS NULL;

--

Joe (Microsoft MVP - XML)

Neal · February 4th, 2008, 10:09 AM

Thanks guys for the prompt responses - Much appreciated.

I'll try them this afternoon...

Neal

A Northern Soul