I'm trying to bulk insert data into a table using the following script and I get a Primary Key Constraint Error... something like " violation of primary key constraint cannot insert duplicate values".  I can get around this by truncating the table and running the bulk insert again but the issue with doing that is I loose all the previous data.  All I'm trying to do is get a script that will allow me to bulk insert data into my table without getting the above error.  The purpose of the bulk insert is to replace pricing in a table with new pricing that changes monthly.  Currently I have to do it manually from the software that writes to the sql tables.  Please help!!!

BULK
insert dbo.test
FROM 'c:\test.csv'
WITH
(
KEEPIDENTITY,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Hi there,

It seems you need to add formatfile in your script as well. I have not tried it.

please refer to the link below:

https://msdn.microsoft.com/en-us/library/ms186335.aspx

Good Luck

I would recommend to load the data into TEST_STG(truncate and load the data into Staging table) and then validate with test table. Either you can remove the duplicates Or you can use MERGE statement

Please refer the below link

https://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/

--Prashanth

***Prashanth***

Im a SQL newbie can you give me an example script to use?  Where would the "IF MATCHED" go in relation to the below scripts

BULK
insert dbo.emp
FROM 'c:\empimports.csv'
WITH
(
KEEPIDENTITY,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

First create TEST_STG same structure as of TEST TABLE.

Truncate and Load the data into TEST_STG table using bulk insert

BULK
insert dbo.emp_STG
FROM 'c:\empimports.csv'
WITH
(
KEEPIDENTITY,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Then run the below merge statement

MERGE TEST T
USING TEST_STG TS 
ON T.PK_COLUMN= TS.PK_COLUMN
WHEN MATCHED THEN
  UPDATE
  SET T.Col1=TS.col1,T.col2=TS.col2
WHEN NOT MATCHED BY TARGET THEN
  INSERT (Col1,col2,col3)
  VALUES (TS.Col1,TS.col2,TS.col3);
 

--Prashanth