How to insert values into a table?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to insert values into a table??

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-10-26 : 08:41:32

Hello all,
I have written query which need to insert in bulk i have run the same in one database it worked fine and the same thing i did in another database but it showing error like :
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_CodeSetDrug'. Cannot insert duplicate key in object 'dbo.CodeSetDrug'. The duplicate key value is (81).

In the both Databases table structure and every thing is same.
Why it is showing an error can't figure it out....

My query :
insert into CodeSetDrug
(
DrugCode ,
DrugCodeType ,
DrugName ,
DrugDescription ,
ListingSequenceNo ,
CreatedByUserId
)
SELECT DISTINCT
ndc_code ,
ndc_code ,
brand_name ,
brand_name ,
Listings.ListingSequenceNo ,
1
FROM ccmsageuat.dbo.NewNDCCodes new
INNER JOIN Listings
ON Listings.MarketingCategoryName = new.category
AND Listings.GenericName = new.generic_product_name
WHERE NOT EXISTS (SELECT 1
FROM CodeSetDrug
WHERE DrugCode = ndc_code
AND DrugName = brand_name
AND ListingSequenceNo = Listings.ListingSequenceNo
)
GO

P.V.P.MOhan

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-26 : 09:08:01

One obvious issue is that the PK is a single column but your distinct and existence check are on multiple columns.
Try checking dta in the table and the query for PK = 81.

I would give the queries but you don't say what the PK is.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-10-26 : 10:13:44

the primary key is Drug Code ID

P.V.P.MOhan

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-10-26 : 10:19:31

while i am exceuting the same query the error is increasing duplicate value 81 to 82 to 83
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_CodeSetDrug'. Cannot insert duplicate key in object 'dbo.CodeSetDrug'. The duplicate key value is (82).
why it is showing like that ???
i know that primary key won't accept duplicates but here we need to keep duplicates drug codes...

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-30 : 02:57:10

Show us the structure of 'dbo.CodeSetDrug' and 'ccmsageuat.dbo.NewNDCCodes' tables

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 09:31:53

quote:
Originally posted by mohan123

while i am exceuting the same query the error is increasing duplicate value 81 to 82 to 83
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_CodeSetDrug'. Cannot insert duplicate key in object 'dbo.CodeSetDrug'. The duplicate key value is (82).
why it is showing like that ???
i know that primary key won't accept duplicates but here we need to keep duplicates drug codes...

P.V.P.MOhan

then you should reddefine your primary key based on your rules. may be create composite key over drug_code,brand_name etc

The reason why id changes is each time it tries to insert which generates the new id and then violation of PK constraint occurs which rollbacks the transaction. So sequential ID value generated gets incremented automatically.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources