September 29, 2015 at 11:43 am
Folks,
I know there is a command that you can use to tell SQL server to stop throwing the following error message ( Something like IGNORE WITH ???... );
Help me please.
So I am trying to INSERT and some records may violate the primary key constraint. That is OK. But I do need the rest of the records to
get inserted and not for the script to stop by giving the following error message.
Msg 2627, Level 14, State 1, Line 14
Violation of PRIMARY KEY constraint 'members_pk'. Cannot insert duplicate key in object 'dbo.Member_Info'. The duplicate key value is (10000001, 00010000001).
The statement has been terminated.
September 29, 2015 at 11:58 am
You can set an index option to Ignore_dupe_key but with a constraint you may need to disable it or drop it.
September 29, 2015 at 12:00 pm
ALTER TABLE TABLE_NAME NOCHECK CONSTRAINT ALL may work.
But, just out of curiosity, if it's ok to have data in the table that violates the primary key, what's the point of having a primary key???
Drop the primary key, or, change it to something else.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 29, 2015 at 12:04 pm
No ...
When I do an insert I only want the rows that don't violate the primary key to get inserted.
What happens is when I do an insert to table A from TABLE B,
Then B might have some good records as well as ones that violate the primary key of A
September 29, 2015 at 12:06 pm
CREATE TABLE [dbo].[Member_Info]
(
[MemberID][int]NOT NULL,
[MemberNbr][char](11)NOT NULL,
[M_MIN_EligibilityStartDate][int]NULL,
[M_MAX_EligibilityEndDate][int]NULL,
[MM_MIN_EligibilityStartDate][int]NULL,
[MM_MAX_EligibilityEndDate][int]NULL
CONSTRAINT members_pk PRIMARY KEY ([MemberID],[MemberNbr])
);
INSERT INTO [Member_Info] ( [MemberID], [MemberNbr],[M_MIN_EligibilityStartDate], [M_MAX_EligibilityEndDate] )
select [MemberID], [MemberNbr],
MIN(M.EligibilityStartDate) as [M_MIN_EligibilityStartDate],
MAX(case when M.EligibilityEndDate = 0 then 99999999 else M.EligibilityEndDate end ) as [M_MAX_EligibilityEndDate]
FROM Member M
WHERE MemberNbr <> ''
GROUP BY MemberID, MemberNbr;
INSERT INTO [Member_Info] ( [MemberID], [MemberNbr],[MM_MIN_EligibilityStartDate], [MM_MAX_EligibilityEndDate] )
select MemberID, MemberNbr,
MIN(M.MonthBeginDate) as [MM_MIN_EligibilityStartDate],
MAX(case when M.MonthEndDate = 0 then 99999999 else M.MonthEndDate end ) as [MM_MAX_EligibilityEndDate]
FROM MemberMonth M
WHERE MemberNbr <> ''
GROUP BY MemberID, MemberNbr;
I want the second insert to succeed... However if there are records that violate the primary key, just keep quiet. No need to throw error messages.
However for now I can use the following alternative for the second insert....
INSERT INTO [Member_Info] ( [MemberID], [MemberNbr],[MM_MIN_EligibilityStartDate], [MM_MAX_EligibilityEndDate] )
select MemberID, MemberNbr,
MIN(M.MonthBeginDate) as [MM_MIN_EligibilityStartDate],
MAX(case when M.MonthEndDate = 0 then 99999999 else M.MonthEndDate end ) as [MM_MAX_EligibilityEndDate]
FROM MemberMonth M
WHERE MemberNbr <> ''
AND NOT EXISTS (SELECT 1 FROM [Member_Info] AS M_INFO WHERE M_INFO.MemberID = M.MemberID AND M_INFO.MemberNbr = M.MemberNbr )
GROUP BY MemberID, MemberNbr;
September 29, 2015 at 12:11 pm
INSERT INTO [Member_Info] ( [MemberID], [MemberNbr],[MM_MIN_EligibilityStartDate], [MM_MAX_EligibilityEndDate] )
select MemberID, MemberNbr,
MIN(M.MonthBeginDate) as [MM_MIN_EligibilityStartDate],
MAX(case when M.MonthEndDate = 0 then 99999999 else M.MonthEndDate end ) as [MM_MAX_EligibilityEndDate]
FROM MemberMonth M
WHERE MemberNbr <> ''
AND NOT EXISTS (SELECT 1 FROM [Member_Info] AS M_INFO WHERE M_INFO.MemberID = M.MemberID AND M_INFO.MemberNbr = M.MemberNbr )
GROUP BY MemberID, MemberNbr;
This is the correct way to do things. There is no such thing as a "quiet the errors" setting that I know of. Prevent the insertion of bad records in your statement.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 29, 2015 at 12:28 pm
I'm not aware of any way to do this. If you're attempting to insert data that violates a primary key, it's a very serious error.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 29, 2015 at 12:44 pm
Finally Found IT... Here you go ... That little extra IGNORE_DUP_KEY will do the trick.
So if you attempt tp violate the primary key.. SQL Server will be quiet and just ignore.. Not throw any error messages...
CREATE TABLE [dbo].[Member_Info]
(
[MemberID][int]NOT NULL,
[MemberNbr][char](11)NOT NULL,
[M_MIN_EligibilityStartDate][int]NULL,
[M_MAX_EligibilityEndDate][int]NULL,
[MM_MIN_EligibilityStartDate][int]NULL,
[MM_MAX_EligibilityEndDate][int]NULL,
[MIN_EligibilityStartDate][int]NULL,
[MAX_EligibilityEndDate][int]NULL
CONSTRAINT members_pk PRIMARY KEY ([MemberID],[MemberNbr]) WITH ( IGNORE_DUP_KEY=ON )
);
September 29, 2015 at 12:47 pm
Run this and you will see what I mean ... No errors and the output will be 2 rows...
IT WORKS
CREATE TABLE #t
(
[MemberID][int]NOT NULL,
[MemberNbr][char](11)NOT NULL,
[M_MIN_EligibilityStartDate][int]NULL,
[M_MAX_EligibilityEndDate][int]NULL,
[MM_MIN_EligibilityStartDate][int]NULL,
[MM_MAX_EligibilityEndDate][int]NULL,
[MIN_EligibilityStartDate][int]NULL,
[MAX_EligibilityEndDate][int]NULL
CONSTRAINT members_pk PRIMARY KEY ([MemberID],[MemberNbr]) WITH ( IGNORE_DUP_KEY=ON )
);
INSERT INTO #t( [MemberID], [MemberNbr] ) VALUES ( 1, '12345678901' );
INSERT INTO #t( [MemberID], [MemberNbr] ) VALUES ( 1, '12345678901' );
INSERT INTO #t( [MemberID], [MemberNbr] ) VALUES ( 1, '12345678901' );
INSERT INTO #t( [MemberID], [MemberNbr] ) VALUES ( 2, '42345678901' );
Select * FROM #t;
September 29, 2015 at 12:51 pm
What's the point? What problem are you trying to solve?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 29, 2015 at 12:56 pm
The point is ...
I don't have to worry about any inserts that fail due to violation of the primary key... ... No error messages .. that means the script will peacefully run....and get to the next section of code what ever that needs to get done..
September 29, 2015 at 1:41 pm
To be fair, that solution was the very first response to your question 🙂
September 29, 2015 at 4:47 pm
Huh. Cool. Always learning. That's the first time I've seen that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 1, 2015 at 10:42 am
Have you experimented with the MERGE statement? I've never used it, but it seems to me that (A) the WHEN MATCHED could be used to set a value to itself that is coming from your update, and (B) you wouldn't have to change your dup key constraint.
BOL says "Because the MERGE statement performs a full table scan of both the source and target tables, I/O performance can be affected when using the TOP clause to modify a large table by creating multiple batches. In this scenario, it is important to ensure that all successive batches target new rows.", I have no idea if this would be too much of a performance hit for your environment.
Again, not having used it, I have no idea if it would be a viable approach.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply