Primary Key Violation Error - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Primary Key Violation Error

Author

Topic

satish15385
Starting Member

17 Posts

Posted - 2011-09-13 : 13:57:06

I have the Following table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Master_Race](
[emplid] [varchar](11) NOT NULL,
[Race] [varchar](1) NOT NULL,
CONSTRAINT [PK_Master_Race_1] PRIMARY KEY CLUSTERED
(
[emplid] ASC,
[Race] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

when i am trying to Insert the values into the tables, it says violation error on Primary key Constraint.

I am trying to import rows from diff table and it has duplicated emplid for few rows

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 14:01:18

make sure you bring only unique combination of emplid,Race from other table. Also make sure the combination you're trying to insert doesnt already exist in destination table by doing some thing like


INSERT INTO Master_Race (emplid,Race,..)
SELECT emplid,Race, other columns...
FROM sourcetable s
WHERE NOT EXISTS (SELECT 1 FROM Master_Race WHERE emplid = s.emplid AND Race = s.Race)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satish15385
Starting Member

17 Posts

Posted - 2011-09-13 : 14:12:55

I tried exactly what u Suggested but still its throwing me this error :

Violation of PRIMARY KEY constraint 'PK_Master_Race_1'. Cannot insert duplicate key in object 'dbo.Master_Race'.
The statement has been terminated.

In the Sourcetable s : I have couple of rows with same emplid but diff race. for ex below :

00000064890 2
00000064890 3

I want both records to insert in the master_race table...Please help me thanks a lot in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 14:20:30

that shouldnot be a problem as your pk is on composite combination of emplid,race. since race is different it wont cause any harm.

run the below query and post the result


SELECT *
FROM
(
SELECT COUNT(1) OVER (PARTITION BY emplid,Race) AS Cnt, emplid,Race, other columns...
FROM sourcetable s
WHERE NOT EXISTS (SELECT 1 FROM Master_Race WHERE emplid = s.emplid AND Race = s.Race)
)t
WHERE Cnt >1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources