Violation of PRIMARY KEY constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Violation of PRIMARY KEY constraint

Author

Topic

psitba
Starting Member

6 Posts

Posted - 2009-09-22 : 14:51:28

I'm trying to copy data from one table to another table with almost the same columns name... but i'm getting this error msg:

The statement has been terminated.
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_tEEOCategory'. Cannot insert duplicate key in object 'dbo.tEEOCategory'.

my statement:

INSERT INTO PSCHCS03.PS5293Sample.dbo.tEEOCategory(

EEOCategoryCode,

EEOCategoryDescription,

SortOrder,

EEOCategoryGUID,

EOSurveySortOrder,

InactiveFlag,

[RowVersion]

)

select

EEOCategoryCode,

EEOCategoryDescription,

SortOrder,

EEOCategoryGUID,

EOSurveySortOrder,

InactiveCodeFlag,

1 AS [RowVersion]

from

tEEOCategory

select * from tEEOCategory

Please help!!

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-09-22 : 15:11:28

You're trying to insert a EEOCategoryCode which already exists in the table, that's why you're getting a primary key violation.

Some days you're the dog, and some days you're the fire hydrant.

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-22 : 15:49:34

What's the key of PSCHCS03.PS5293Sample.dbo.tEEOCategory

????

Post the DDL

BUT, the row obviously exists...do you want to do an update instead?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

psitba
Starting Member

6 Posts

Posted - 2009-09-22 : 16:07:41

Here's my DDL
CREATE TABLE [dbo].[tEEOCategory](
[EEOCategoryCode] [dbo].[shrsCode] NOT NULL,
[EEOCategoryDescription] [dbo].[shrsDescription] NOT NULL,
[SortOrder] [nvarchar](15) NULL,
[EEOCategoryGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[EOSurveySortOrder] [nvarchar](15) NULL,
[InactiveFlag] [dbo].[shrsFlag] NOT NULL,
[RowVersion] [int] NOT NULL,
CONSTRAINT [PK_tEEOCategory] PRIMARY KEY CLUSTERED
(
[EEOCategoryCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [RG_tEEOCategory] UNIQUE NONCLUSTERED
(
[EEOCategoryGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

let me try the UPDATE ..thanks for the suggestion

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-22 : 16:24:52

You do know that you are taking everything from that table, tEEOCategory

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-22 : 16:26:46

[CODE]

INSERT INTO PSCHCS03.PS5293Sample.dbo.tEEOCategory (
EEOCategoryCode
, EEOCategoryDescription
, SortOrder
, EEOCategoryGUID
, EOSurveySortOrder
, InactiveFlag
, [RowVersion]
)
SELECT EEOCategoryCode
, EEOCategoryDescription
, SortOrder
, EEOCategoryGUID
, EOSurveySortOrder
, InactiveCodeFlag
1 AS [RowVersion]
FROM tEEOCategory
WHERE EEOCategoryGUID NOT IN (
SELECT EEOCategoryGUID
FROM PSCHCS03.PS5293Sample.dbo.tEEOCategory)

[/CODE]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

psitba
Starting Member

6 Posts

Posted - 2009-09-22 : 16:39:08

quote:
Originally posted by X002548

You do know that you are taking everything from that table, tEEOCategory

Brett

8-)

Yes, I figure it wouldn't hurt and just in case you ask me for more stuff.

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-22 : 16:41:40

Well, what I meant was...Are you sure that none of the key data exists in the table you are inserting into?

It appears that it does...

Run this and tell us what it gives you


SELECT COUNT(*)
  FROM	tEEOCategory
 WHERE EEOCategoryGUID IN (
			SELECT  EEOCategoryGUID
			  FROM  PSCHCS03.PS5293Sample.dbo.tEEOCategory)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

psitba
Starting Member

6 Posts

Posted - 2009-09-22 : 16:57:47

it give me '29'

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-22 : 17:02:44

Well that mean that 29 rows of data already exists with that key in the table you are trying to insert into, and that won't work

And what does

SELECT COUNT(*) FROM tEEOCategory
SELECT COUNT(*) FROM PSCHCS03.PS5293Sample.dbo.tEEOCategory)

Give you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

psitba
Starting Member

6 Posts

Posted - 2009-09-22 : 17:07:41

SELECT COUNT(*) FROM tEEOCategory = 29
SELECT COUNT(*) FROM PSCHCS03.PS5293Sample.dbo.tEEOCategory = 29

psitba
Starting Member

6 Posts

Posted - 2009-09-22 : 17:35:37

quote:
Originally posted by psitba

SELECT COUNT(*) FROM tEEOCategory = 29
SELECT COUNT(*) FROM PSCHCS03.PS5293Sample.dbo.tEEOCategory = 29

Brett, Thank you very much for your help

I know what need to be done now.

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-23 : 10:00:34

Well good luck...and welcome to SQLTeam!

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources