Populating existing table. - 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

Populating existing table.

Author

Topic

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-07 : 19:40:21

Need to populate a table with values from another table. Its not liking the word AS. Asl oif I remove that it gives the following error

Error Message
=========================
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_int_upc_ean'. Cannot insert duplicate key in object 'dbo.int_upc_ean'.
The statement has been terminated.

Script:
=================
INSERT INTO dbo.int_upc_ean (frcst_altr_id, ean_cd, upc)
AS
select frcst_altr_id, ean_cd, upc
from dbo.ean_cd a
left join dbo.ext_upc_staging b
on a.frcst_altr_id = b.productCode
order by frcst_altr_id

GO

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-07 : 21:07:47

Since this is development I deleted all the records. Still it gave me the PK error. Finally I removed the PK feature and then I was able to populated the table. Is that how you are suppose to do?

What if you have an existing table with data (2 scenarios)

1 With duplicate possibility in the existing table. How do you have get a fianl table with only unique PK records.
2 when u know for sure there are no duplicates in the existing data as was the case for me. - obviously it did not work.

SA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-08 : 13:06:58

When my insert errors duplicate key I always look at the select statement I have used to populate the table.
You can check your data as follows:
I assume that frcst_altr_id is the problem key


select frcst_altr_id,count(*)
from
(select frcst_altr_id, ean_cd, upc
 from dbo.ean_cd a
 left join dbo.ext_upc_staging b
 on a.frcst_altr_id = b.productCode )dt
group by frcst_altr_id
having count(*) > 1

No, you're never too old to Yak'n'Roll if you're too young to die.

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-10 : 11:59:45

Thanks for the input. I will try this.

SA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources