Primary Key Violation when Loading Table

Question

Primary Key Violation when Loading Table

Welsh Corgi

SSC Guru

Points: 116520
More actions
June 8, 2016 at 9:20 am

#326858

I am getting a Primary Key Violation when Loading a Table.
I queried the source table and there is only one record with value of the primary key.
This does not make sense.
Any ideas?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

Welsh Corgi (6/8/2016)
I am getting a Primary Key Violation when Loading a Table.
I queried the source table and there is only one record with value of the primary key.
This does not make sense.
Any ideas?

check your source; you could have two of the same value in your temp/staging table before it is being loaded into your Target table; you might need to filter your source prior to insert.

are you joining your staging to target to eliminate inserting duplicates?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Thanks. I checked it that does ot apply.

Select tblNexus.* from TblNexus WITH (NOLOCK)

Inner Join PrismClients WITH (NOLOCK) on TblNexus.PrismclientID = TblNexus.PrismClientID and PrismClients.CountryID = 2

It just happened with another table, a simple lookup table.

I do this all of the time and I do not have any problems.

I tried the import wizard and it is not showing me the source table to select from.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I added a distinct on the lookup table and I did not get duplicates.

SELECT DISTINCT Catevent.*

FROM CatEvent

Inner join CatEventArea on CatEventArea.CatEventID = CatEvent.CatEventID and CATEventArea.CountryID =

Thank you Sir.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Welsh Corgi (6/8/2016)
I am getting a Primary Key Violation when Loading a Table.
I queried the source table and there is only one record with value of the primary key.

In that case, there must be one row in the source table with that PK value, and one row already in the destination table.

John

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

John Mitchell-245523 (6/8/2016)
Welsh Corgi (6/8/2016)
I am getting a Primary Key Violation when Loading a Table.
I queried the source table and there is only one record with value of the primary key.
In that case, there must be one row in the source table with that PK value, and one row already in the destination table.
John

I'm truncating the tables prior to the load.

My source query was the offender.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Welsh Corgi (6/8/2016)
John Mitchell-245523 (6/8/2016)
Welsh Corgi (6/8/2016)
I am getting a Primary Key Violation when Loading a Table.
I queried the source table and there is only one record with value of the primary key.
In that case, there must be one row in the source table with that PK value, and one row already in the destination table.
John
I'm truncating the tables prior to the load.
My source query was the offender.
Thanks.

Just for clarity, that is exactly what Lowell told you to check.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

I execute the following query:

SELECT DISTINCT ID, Name, Abbreviation, CountryID

FROM Postal_StateProvince

WHERE CountryID = 2

It returns the following values:

IDNameAbbreviationCountryID

52AlbertaAB2

53British ColumbiaBC2

54ManitobaMB2

55New BrunswickNB2

56NewfoundlandNL2

57Northwest TerritoriesNT2

58Nova ScotiaNS2

59NunavutNU2

60OntarioON2

61Prince Edward IslandPE2

62QuebecQC2

63SaskatchewanSK2

64Yukon TerritoryYT2

It return the following error:

"Cannot insert the value NULL into column 'Abbreviation', table 'PrismDataCanada.dbo.Postal_StateProvince'; column does not allow nulls. INSERT fails.".

I create an import wizard and I do not get an error.

That is weird?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323547 More actions · Answer 8

import wizard is probably dropping and recreating the tables;

the existing table says NOT NULL,

but whatever the wizard creates allows nulls.

pretty easy to check, compare the table definitions.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Welsh Corgi (6/8/2016)
I execute the following query:
SELECT DISTINCT ID, Name, Abbreviation, CountryID
FROM Postal_StateProvince
WHERE CountryID = 2
It returns the following values:
IDNameAbbreviationCountryID
52AlbertaAB2
53British ColumbiaBC2
54ManitobaMB2
55New BrunswickNB2
56NewfoundlandNL2
57Northwest TerritoriesNT2
58Nova ScotiaNS2
59NunavutNU2
60OntarioON2
61Prince Edward IslandPE2
62QuebecQC2
63SaskatchewanSK2
64Yukon TerritoryYT2
It return the following error:
"Cannot insert the value NULL into column 'Abbreviation', table 'PrismDataCanada.dbo.Postal_StateProvince'; column does not allow nulls. INSERT fails.".
I create an import wizard and I do not get an error.
That is weird?

What is returning an error? I see nothing inserting anything into anything in the quoted post.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Lowell (6/8/2016)
import wizard is probably dropping and recreating the tables;
the existing table says NOT NULL,
but whatever the wizard creates allows nulls.
pretty easy to check, compare the table definitions.

The wizard appends the record to the table.

I save the Import package and added it to my solution.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 11

Welsh Corgi (6/8/2016)
I execute the following query:
SELECT DISTINCT ID, Name, Abbreviation, CountryID
FROM Postal_StateProvince
WHERE CountryID = 2
It returns the following values:
IDNameAbbreviationCountryID
52AlbertaAB2
53British ColumbiaBC2
54ManitobaMB2
55New BrunswickNB2
56NewfoundlandNL2
57Northwest TerritoriesNT2
58Nova ScotiaNS2
59NunavutNU2
60OntarioON2
61Prince Edward IslandPE2
62QuebecQC2
63SaskatchewanSK2
64Yukon TerritoryYT2
It return the following error:
"Cannot insert the value NULL into column 'Abbreviation', table 'PrismDataCanada.dbo.Postal_StateProvince'; column does not allow nulls. INSERT fails.".
I create an import wizard and I do not get an error.
That is weird?

First, that's not a Primary Key violation.

Second, a SELECT cannot return an error about inserts. You've not posted the entire code that you were using so it's not possible to figure out if it's weird or not. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)