PK - data migration - sql server

Posted on 2011-10-11
Last Modified: 2012-05-12

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

Question by:shmz
    LVL 16

    Expert Comment

    by:Easwaran Paramasivam
    I hope in Type table you have composite primary key.

    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.

    LVL 39

    Accepted Solution

    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 DISTINCT Type,Entry FROM Type ) q
    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

    Author Closing Comment


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now