Primary key violation constraint

Question

string sqlInsert = "Insert into account_details(
     account_number,
     account_type,
     transfer_access_code,
     account_balance,
     customer_id) 
SELECT 
     account_number,
     account_type,
     transfer_access_code,
     account_balance,
     customer_id 
from customer_details";

This query just takes data from customer details(table1) and insert's it in other table2(account_details)

when this query is fired for first time it works fine

but when fired second time it show error Violation of PRIMARY KEY constraint 'PK_account_details'. Cannot insert duplicate key in object 'dbo.account_details'.

any idea to skip existing data in (account_details)table1 and inserting new data in next row

Answer 1

Using a LEFT JOIN allows you to exclude all the rows that are already present in the account details table. An alternative to this (and probably even faster) is using a NOT EXISTS.

using a LEFT JOIN

INSERT INTO account_details (
    account_number
    , account_type
    , transfer_access_code
    , account_balance
    , customer_id) 
SELECT  account_number
        , account_type
        , transfer_access_code
        , account_balance
        , customer_id 
FROM    customer_details cd
        LEFT OUTER JOIN account_details ad ON ad.Account_number = cd.account_number
WHERE   ad.account_number IS NULL        

using a NOT EXISTS

INSERT INTO account_details (
    account_number
    , account_type
    , transfer_access_code
    , account_balance
    , customer_id) 
SELECT  account_number
        , account_type
        , transfer_access_code
        , account_balance
        , customer_id 
FROM    customer_details cd
WHERE   NOT EXISTS (
          SELECT   * 
          FROM     account_details 
          WHERE    account_number = cd.account_number)

Answer 2

assuming Account_Number is the primary key, this should work

string sqlInsert = "Insert into account_details(
     account_number,
     account_type,
     transfer_access_code,
     account_balance,
     customer_id) 
SELECT 
     cd.account_number,
     cd.account_type,
     cd.transfer_access_code,
     cd.account_balance,
     cd.customer_id 
from customer_details cd
left outer join account_details ad
    on cd.account_number = ad.account_number
where ad.account_number is NULL";

Answer 3

INSERT INTO account_details (ad.account_number, ad.account_type, 
    ad.transfer_access_code, ad.account_balance, ad.customer_id) 
SELECT cd.account_number, cd.account_type, 
    cd.transfer_access_code, cd.account_balance, cd.customer_id
FROM customer_details cd
LEFT OUTER JOIN account_details ad 
    ON cd.account_number = ad.account_number 
WHERE ad.account_number is NULL