Re: BULK INSERT ignores UNIQUE index with IGNORE_DUP_KEY set?

Weyus (weyus@xxxxxxx) writes:
Q1) Will a regular INSERT that attempts to insert duplicate data get
an error back or just a warning?
...
However, my question Q1 still stands - what is the behavior of an
INSERT of duplicate data against this type of index - I suspect that
you get a warning and not an error - is that correct?

Yes, SQL Server only emits a warning. However, there is a catch, some
client APIs incorrectly interprets this as an error. If run:

CREATE TABLE blamblam (a int NOT NULL)
CREATE UNIQUE INDEX updix ON blamblam(a) WITH IGNORE_DUP_KEY
go
EXEC master..xp_cmdshell 'ECHO 12 > C:\temp\blamblam.txt'
EXEC master..xp_cmdshell 'ECHO 12 >> C:\temp\blamblam.txt'
go
BULK INSERT blamblam FROM 'C:\temp\blamblam.txt'
go
SELECT * FROM blamblam
go
DROP TABLE blamblam

I get this output from Mgmt Studio:

Duplicate key was ignored.

(1 row(s) affected)
a
-----------
12

(1 row(s) affected)

But from Query Analyzer, against the same server instance, I get:


Server: Msg 3604, Level 16, State 1, Line 1
Duplicate key was ignored.

a
-----------
12
(1 row(s) affected)


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: installing SQL 2008 x64 on Vista Enterprise x64-Firewall warnings
    ... I can't see the warning at the moment but my recollection is that is just warns you that the firewall is on and you might have to configure it. ... Since setup doesn't know what you want to do with SQL Server it can't tell if you have configured it correctly. ... How can I get rid of this warning and continue with my installation? ... 135 TCP - SQL Server Integration Services ...
    (microsoft.public.sqlserver.setup)
  • Re: DTS Speed Issues
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. ... My plan is to write an app in .Net as you suggested to monitor> the filesystem from what I can tell via MSDN documentation it will watch all> the subdirectories.. ... don't know if I'll go as far as to create a DFS because> its always uploading to the same network directory. ... My next Question is this,> there are duplicate filenames but in the original directory structure they> are in specific folders so that isn't a problem. ...
    (microsoft.public.sqlserver.dts)
  • Re: Stupid SQLCE warning message
    ... you just pretend you did not see ... >> I am trying to replicate SQL Server Database to SQLCE and solving one ... >>>Incorrect Deployment Warning ... >> How can I ignore this messae and start application in debug mode from ...
    (microsoft.public.dotnet.framework.compactframework)
  • RE: Import Warning Message
    ... I had the same problem when I set up a proxy account to use the ssis service ... My Source DB is on a hosted remote sql server 2005. ... Warning 0x80049304: Data Flow Task 1: Warning: Could not open global shared ...
    (microsoft.public.sqlserver.dts)
  • Stupid SQLCE warning message
    ... I am trying to replicate SQL Server Database to SQLCE and solving one ... Last one is stupid SQL CE warning: ... than on the development computer. ... >Consider upgrading to the latest version. ...
    (microsoft.public.dotnet.framework.compactframework)
Loading