By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 2,004 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

BULK INSERT ignores UNIQUE index with IGNORE_DUP_KEY set?

P: n/a
All,

Just want to make sure that I understand what's going on here.

I have a table with IGNORE_DUP_KEY set on a unique, multi-column
index.

What I'm seeing is this:

1) When performing a BULK INSERT, the UNIQUE index is not being
respected and rows which violate the unique index are inserted.

2) When performing a regular INSERT, the UNIQUE index is being
respected and rows which violate the unique index ARE NOT inserted.

Is this expected behavior.

Also, I have some questions, given the index described.

Q1) Will a regular INSERT that attempts to insert duplicate data get
an error back or just a warning?

Q2) How can I set things up so that a BULK INSERT would NOT allow
duplicates to be entered into the table?

Thanks,
Wes Gamble

Jun 22 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Jun 22, 5:33 pm, Weyus <w...@att.netwrote:
All,

Just want to make sure that I understand what's going on here.

I have a table with IGNORE_DUP_KEY set on a unique, multi-column
index.

What I'm seeing is this:

1) When performing a BULK INSERT, the UNIQUE index is not being
respected and rows which violate the unique index are inserted.

2) When performing a regular INSERT, the UNIQUE index is being
respected and rows which violate the unique index ARE NOT inserted.

Is this expected behavior.

Also, I have some questions, given the index described.

Q1) Will a regular INSERT that attempts to insert duplicate data get
an error back or just a warning?

Q2) How can I set things up so that a BULK INSERT would NOT allow
duplicates to be entered into the table?

Thanks,
Wes Gamble
I made a logical error that I just realized. The BULK INSERT that I
was doing, in fact did have unique enough data.

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?

Thanks,
Wes

Jun 22 '07 #2

P: n/a
Weyus (we***@att.net) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 23 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.