bcp into table having unique constraint

[Matsul]

I need to load a file into a table knowing that there are some duplicates on a column having a unique constraint. I set -m but it still exiting without loading the non duplicate rows.

Any ideas how I can get bcp to load the non duplicate rows ?

H:\>bcp nevent.dbo.nrocess in c:\temp\charge_20070308_0353.csv.wri -SW104 -e c:\temp\charge.import.err.wri -m 9999 -Tc -t","

Starting copy...
SQLState = 23000, NativeError = 2627
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
constraint 'ne_unique'. Cannot insert duplicate key in object 'NROCESS'.
SQLState = 01000, NativeError = 3621
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.

BCP copy in failed

 

[AlexCuse]

I would do it in four steps:

bcp it into a temp table,
clean out the duplicates,
insert from temp table into your destination,
drop temp table

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[mrdenny]

Dito.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com