主键违规约束

发布于 2024-09-25 09:10:04 字数 553 浏览 1 评论 0原文

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";

此查询仅从客户详细信息（table1）中获取数据，并将其插入到其他表2（account_details）中

，当第一次触发此查询时，它工作正常，

但第二次触发时，它显示错误违反主键约束“PK_account_details”。无法在对象“dbo.account_details”中插入重复的键。

任何想法可以跳过 (account_details)table1 中的现有数据并在下一行中插入新数据

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

百合的盛世恋 2024-10-02 09:10:04

使用LEFT JOIN 允许您排除帐户详细信息 表中已存在的所有行。替代方案（可能甚至更快）是使用 NOT EXISTS。

使用 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

使用 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)

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)

回复收藏 0 原文

甜妞爱困 2024-10-02 09:10:04

假设 Account_Number 是主键，这应该可以工作

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";

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";

回复收藏 0 原文

┼── 2024-10-02 09:10:04

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

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

回复收藏 0 原文

~没有更多了~