Primary key violation constraint
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 i开发者_如何学编程n object 'dbo.account_details'.
any idea to skip existing data in (account_details)table1 and inserting new data in next row
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)
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";
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
精彩评论