|
|
Posted by Erland Sommarskog on 06/23/07 09:09
Weyus (weyus@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, esquel@sommarskog.se
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
Navigation:
[Reply to this message]
|