Trying to insert data from one table to another

• Mond

  Old Hand

  Points: 356

  More actions

  February 28, 2020 at 6:15 pm

  #3729672

  Hello, I think this is a simple query but it is being a long time I haven't write queries. If you can help me I will appreciate it a lot.

  I need to copy some data from one table to another table. This is the statement I am trying to use:

  insert into ItemDetail(ItemMasterId,Content,ItemMasterAttributeId)

  Select distinct itemid,Qty,Itemattributeid from #tempitemdetail

  and Im getting the following error:

  Msg 2627, Level 14, State 1, Line 86

  Violation of PRIMARY KEY constraint 'PK_ItemDetail'. Cannot insert duplicate key in object 'dbo.ItemDetail'. The duplicate key value is (11171, 18).

  The statement has been terminated.

   

  The table Itemdetail has a lot of attributes for same Itemid. I just need to add a new value for a new attribute id.

   

  Could you help me to figure it out how to write my query to work? Thanks

   

   

   

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  February 28, 2020 at 7:00 pm

  #3729686

  We don't have enough information to tell you exactly what the problem is, but the error message gives you a LOT of information.

  1. Your table dbo.ItemDetail has a primary key PK_ItemDetail.

     1. We don't know what makes up your primary key, but I'm guessing from the error and the column names that it is ItemMasterId and ItemMasterAttributeId.

  2. You have a violation in your primary key.  This can result from two possibilities (and it's possible to have both at the same time).

     1. You already have a record  in your table with the value pair (11171, 18).
     2. You have two or more records in your source data with the value pair (11171, 18).

  3. We can't tell you what to do to resolve these, because we don't have enough information.

     1. Do you want to update an existing record or leave it as it is?
     2. Which record do you want to use when you have duplicates in your source data?

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA