IGNORE_DUP_KEY in Sql Server 2000 with composite primary key
I'm trying to create a table in SQL Server 2000, that has a composite primary key with IGNORE_DUP_KEY set to ON.
I've tried looking for this option in SQL Server Management Studi开发者_JAVA百科o Express but I couldn't find it so now I'm down to creating the table programatically. Every SQL command I found on Google or Stack Overflow gives me an error:
Incorrect syntax near '('.
The table should have 4 columns (A,B,C,D) all decimal(18) and I need the primary key on A,B,C.
I would appreciate if someone could post an example CREATE command.
create table MyTable2 (
[a] decimal(18,2) not null,
[b] decimal(18,2) not null,
[c] decimal(18,2) not null,
[d] decimal(18,2),
CONSTRAINT myPK PRIMARY KEY (a,b,c)
)
CREATE UNIQUE INDEX MyUniqueIgnoringDups
ON MyTable2 (a,b,c)
WITH IGNORE_DUP_KEY --SQL 2000 syntax
--WITH(IGNORE_DUP_KEY = On) --SQL 2005+ syntax
--insert some data to test.
insert into mytable2 (a,b,c,d) values (1,2,3,4);--succeeds; inserts properly
insert into mytable2 (a,b,c,d) values (1,2,3,5);--insert fails, no err is raised.
-- "Duplicate key was ignored. (0 row(s) affected)"
For anyone interested, here's an explanation of what's happening from Erland Sommarskog on the MSDN forums:
When
IGNORE_DUP_KEY
is OFF, a duplicate key value causes an error and the entire statement is rolled back. That is, if the statement attempted to insert multiple rows, no rows are inserted.When
IGNORE_DUP_KEY
is ON, a duplicate key value is simply ignored. The statement completes successfully and any other rows are inserted.
精彩评论