Click here to Skip to main content
15,565,403 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

I have imported a CSV file into SQL, the wizard has added this as a new table.

I am trying to run a SQL Statement to insert the data from the imported table into the one I need to use in the new data table.

However I am getting the following error.

Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint 'PK_Customer'. Cannot insert duplicate key in object 'dbo.Customer'. The duplicate key value is (105).
The statement has been terminated.


This is my statement:

SQL
INSERT INTO dbo.Customer
(custID,lastName,firstName,title,address1,address2,address3,postCode,email,fax)
SELECT ACCOUNT,SURNAME,FORENAMES,TITLE,ADD1,ADD2,ADD3,POSTCODE,EMAIL,FAX
FROM dbo.[NCCUST CSV FORMAT PFS 9.1.14];


Any pointers would be great :)

Thanks
Posted
Updated 30-Jan-18 20:56pm

Primary key (PK)[^] is to guarantee unique data that are frequently defined on an identity column.

A relational database[^] uses PK
Wiki wrote:
In the relational model, each table schema must identify a column or group of columns, called the primary key, to uniquely identify each row. A relationship can then be established between each row in the table and a row in another table by creating a foreign key, a column or group of columns in one table that points to the primary key of another table. The relational model offers various levels of refinement of table organization and reorganization called database normalization. (See Normalization below.) The database management system (DBMS) of a relational database is called an RDBMS, and is the software of a relational database.


Do not remove any PK if you don't want to lose relationships between tables!

If you want to add customers from other data set, please make sure that customer does not exists in the destanation table. You can achieve that using IN clause[^].

SQL
INSERT INTO DestTable (<fields collection>)
SELECT <fields collection>
FROM SourceTable
WHERE DestTable.ID NOT IN (SELECT ID FROM SourceTable)

or use EXISTS statement[^].
 
Share this answer
 
v2
Comments
Karthik_Mahalingam 10-Jan-14 21:27pm    
congrats Maciej Los for 100K
Maciej Los 11-Jan-14 9:07am    
Thank you ;)
CHill60 11-Jan-14 11:14am    
5'd - the only sensible solution given!
Maciej Los 11-Jan-14 14:50pm    
Thank you, CHill ;)
if you want enter the duplicate (Records)entry then remove the Primary key. alter your table like this type.
SQL
ALTER TABLE Table_Name
DROP CONSTRAINT PrimaryKey_Constaint
 
Share this answer
 
Comments
Glen Childs 10-Jan-14 6:27am    
I couldn't drop the PK - Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Customer'. Cannot insert duplicate key in object 'dbo.Customer'. The duplicate key value is (105).
The statement has been terminated.
Msg 3725, Level 16, State 0, Line 6
The constraint 'PK_Customer' is being referenced by table 'Accounts', foreign key constraint 'FK_CustomerID'.
Msg 3727, Level 16, State 0, Line 6
Could not drop constraint. See previous errors
joginder-banger 10-Jan-14 6:31am    
your primary key Referenced foreign key... Remove first Reference key.
Glen Childs 10-Jan-14 7:00am    
Thanks removing them worked, but how can I add the keys back? As my C# app isnt now able to access the data in those tables.
If you do not want the CustID to be filled from csv file then you can make that field as an identity field and use the following query.

SQL
INSERT INTO dbo.Customer
(lastName,firstName,title,address1,address2,address3,postCode,email,fax)
SELECT SURNAME,FORENAMES,TITLE,ADD1,ADD2,ADD3,POSTCODE,EMAIL,FAX
FROM dbo.[NCCUST CSV FORMAT PFS 9.1.14];



Or else you need to delete the primary key, before deleting the primary key constraint, you have to take out the referential integrity constraint and then drop the primary constraint.
 
Share this answer
 
Remove the Foreign key like this type.
SQL
ALTER TABLE Table_Name
DROP FOREIGN KEY Foreign_Key_ID
 
Share this answer
 
violation of PRIMARY KEY constraint 'PK_LPresent', cannot insert dublicate key in object 'dbo.LPresent'. The dublicate key value is (2), the statement has been terminated Translate to somalia
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900