• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

PK - data migration - sql server


I have the following three tables and relationship as follows:

Category  (1:M) Type  (1:M)  Product

Ctgry(PK)        Name
100                  Stationary
200                  Tool

The category table is already populated with the data.
I need to migrate data from different tables into Type table.
The Type table has two pk which are Type and Entry. So the combination of
the two columns uniquely identifies the record. The resulted Type table should
look like the following:
Type(PK)  Entry(PK)              Name     Ctgry(FK)
1XS            A                XXX        100
1XS            B               YYY        100

The problem is when I try to import data, I get message Insert failed,... violation of primary key,...

I could insert the first record but for the second record I get error message. I am using the query:
Insert into ...
select from .....

how can this be done?

Thanks in advance

1 Solution
Easwaran ParamasivamCommented:
I hope in Type table you have composite primary key. http://sqlzoo.net/howto/source/z.dir/tip241027/i02create.xml

If so, it is possible to enter two different values in the fields.

Verify that the values are unique (in combination of both fields).

Verify the Ctgry value is existing in the Category table.

lcohanDatabase AnalystCommented:
Try using DISTINCT in the select like below but if you have "legal" duplicates in the source table it gets more complicated because you need to decide wich one(s) gets in.

Insert into ...
select distinct from .....

You could run a
select count(*) from
select count(*) from
(SELECT Type,Entry FROM Type ) q

(BTW terible choice in my opinion for the table design as table name = column name)

You could also run a statement like below to see the duplicate pairs of type,entry causing the PK violation

select count(*),[Type],[Entry]
from [type] with(nolock)
group by [Type],[Entry]
having count(*)>1
shmzAuthor Commented:

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now