December 21, 2015 at 1:42 pm
Syntax help please ?
How do I modify the primary key to ignore duplicate key entries ?
December 21, 2015 at 2:09 pm
mw112009 (12/21/2015)
Syntax help please ?How do I modify the primary key to ignore duplicate key entries ?
This should work.
ALTER TABLE [TableName] REBUILD WITH (IGNORE_DUP_KEY = ON)
You do know that only means it won't throw an error if you try to insert a new row a key that already exists right? It doesn't allow you to have two rows with the same primary key.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2015 at 2:54 pm
IGNORE_DUP_KEY = ON doesn't "ignore" duplicate keys per-se.
It simply discards new rows, that would cause a key to be duplicated if it were allowed into the table, without throwing an error. (AKA letting you know that it just discarded some of the data you attempted to insert).
The syntax for altering an existing PK constraint would involve doing an ALTER TABLE DROP CONSTRAINT followed by an ALTER TABLE ADD CONSTRAINT...
December 21, 2015 at 9:28 pm
CREATE TABLE dbo.foo (bar int PRIMARY KEY WITH (FILLFACTOR=90, IGNORE_DUP_KEY = ON))
GO
INSERT dbo.foo VALUES (1)
GO
INSERT dbo.foo VALUES (1)
GO
--gives
(1 row(s) affected)
Duplicate key was ignored.
(0 row(s) affected)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply