Insert Into a table with a PK - 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

General SQL Server Forums

New to SQL Server Programming

Insert Into a table with a PK

Author

Topic

mgiardino
Starting Member

11 Posts

Posted - 2009-07-08 : 13:48:57

I am trying to correct insert errors into a table. I have located the records T1 and data to insert into T2. I understand if I do not specify the PK filed on insert it should auto increment. I get a "Msg 2627, Level 14, State 1, Line 25
Violation of UNIQUE KEY constraint 'IX_EntityDistributionLists'. Cannot insert duplicate key in object 'dbo.EntityDistributionLists'.

Any suggestions for a newby?

Here's my code:

DECLARE @MyProd nvarchar(10),
@MyStartDate datetime,
@MyEndDate datetime,
@AddDate datetime;

Set @AddDate = '7/8/2009'
SET @MyProd = '%720';
SET @MyStartDate = '4/1/2009';
SET @MyEndDate = '7/30/2009';

Select Entity.entityid as EntityId,'1' as DistributionListId, '2' as DistributionListStatusCodeId, '1' as Quantity,
'1' as PrimaryList,orderdate as EffectiveDate, (orderdate + 364) as RenewalDate
, orderheader.AddUserId as AddUserId, @AddDate as AddDate
into #tmpa
from orderdetail
inner join products on orderdetail.productid = products.productid
inner join orderheader on orderdetail.OrderId = orderheader.OrderId
inner join entity on entity.entityid = orderheader.entityid
where prodcode like @MyProd and orderdate between @MyStartDate and @MyEndDate and batchtype1 = 'inet'
and MotivationCode = 'OJGen' and Entity.entityid = '221326'
and not exists(select * from EntityDistributionLists where orderheader.entityid = EntityDistributionLists.entityid
and DistributionListStatusCodeId = '2')

Begin Tran
Insert Into EntityDistributionLists (EntityId,DistributionListId,DistributionListStatusCodeId
,Quantity,PrimaryList,EffectiveDate,RenewalDate,AddUserId,AddDate)

Select Entityid,DistributionListId, DistributionListStatusCodeId, Quantity, PrimaryList
,EffectiveDate, RenewalDate, AddUserId, AddDate
FROM #tmpa
--commit
--rollback

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 13:56:14

i think one of your column has unique constraint on it. first find out column. use query below:-


SELECT ccu.TABLE_NAME,ccu.COLUMN_NAME FROM  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
WHERE ccu.CONSTRAINT_NAME='IX_EntityDistributionLists'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-08 : 14:30:43

quote:
Originally posted by mgiardino

I<snip> I understand if I do not specify the PK filed on insert it should auto increment. <snip>

Only if that PK is an IDENTITY field. Maybe you were implying that, but I just wanted to clarify.

mgiardino
Starting Member

11 Posts

Posted - 2009-07-08 : 14:30:59

EntityId,DistributionListId from table EntityDistributionLists

DistributionListId is not a PK and identity = false. all values in this column are "1"

mgiardino
Starting Member

11 Posts

Posted - 2009-07-08 : 14:33:39

quote:
Originally posted by Lamprey

quote:
Originally posted by mgiardino

I<snip> I understand if I do not specify the PK filed on insert it should auto increment. <snip>
Only if that PK is an IDENTITY field. Maybe you were implying that, but I just wanted to clarify.

The Primary Key field is the identity field.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-08 : 14:40:49

I'm confused.. You are selecting the EntityId and DistributionListId into your temp table and then using those values and inserting into EntityDistributionLists. If the Unique constraint is EntityId and DistributionListId then I would assume that your select is generating mor than one row with the same EntityID.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 14:45:44

quote:
Originally posted by mgiardino

EntityId,DistributionListId from table EntityDistributionLists

DistributionListId is not a PK and identity = false. all values in this column are "1"

then you need to make sure EntityId,DistributionListId combination is unique before inserting. just select only unique groups based on EntityId,DistributionListId from source

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-08 : 15:42:54

do you know how to script your table with indexes?

And what version are your running?

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

mgiardino
Starting Member

11 Posts

Posted - 2009-07-08 : 18:24:19

I have 2005 and I can script a table to the query window.
It details field info in the script. Not sure about with indexes.

quote:
Originally posted by X002548

do you know how to script your table with indexes?

And what version are your running?

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

mgiardino
Starting Member

11 Posts

Posted - 2009-07-08 : 18:30:12

I thought by using; not exists(select * from EntityDistributionLists where orderheader.entityid = EntityDistributionLists.entityid) would eliminate this problem. Do you see anything wrong with this section of the SQL?

I removed "and DistributionListStatusCodeId = '2'" since and inactive list could be there.

Thanks
Mike

posted

quote:
Originally by visakh16

quote:
Originally posted by mgiardino

EntityId,DistributionListId from table EntityDistributionLists

DistributionListId is not a PK and identity = false. all values in this column are "1"

then you need to make sure EntityId,DistributionListId combination is unique before inserting. just select only unique groups based on EntityId,DistributionListId from source

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-08 : 19:11:38

quote:
Originally posted by mgiardino

I thought by using; not exists(select * from EntityDistributionLists where orderheader.entityid = EntityDistributionLists.entityid) would eliminate this problem. Do you see anything wrong with this section of the SQL?

I removed "and DistributionListStatusCodeId = '2'" since and inactive list could be there.

Thanks
Mike

posted
quote:
Originally by visakh16

quote:
Originally posted by mgiardino

EntityId,DistributionListId from table EntityDistributionLists

DistributionListId is not a PK and identity = false. all values in this column are "1"

then you need to make sure EntityId,DistributionListId combination is unique before inserting. just select only unique groups based on EntityId,DistributionListId from source

You said the unique constraint was on EntityId and DistributionListId, but you are doing a NOT EXISTS on the EntityID and DistributionListStatusCodeId so that sould be the problem.

If you remove the DistributionListStatusCodeId from the NOT EXISTS does it still fail?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 09:56:18

i think problem is hardcoding distributionlistid as 1. If you're doing this you need to ensure you insert only unique records based on ENtityId value

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources