Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    6

    Unanswered: Am having script Problems with Duplicates and Inserts

    Ok, here is the situation, I have a view in one database and I want to insert all the data into a table on the same server but in a different database. With a no duplicate insert, cause my target table field ItemID can not be duplicated, also if the ItemID already exists, then I don’t want to import it either.

    So I first wrote a script that looked for duplicates, this worked.

    FROM Coffee.dbo.vueProductCase a
    JOIN (SELECT ProductCode, COUNT(*) AS cnt
    FROM coffee.dbo.vueProductCase
    GROUP BY ProductCode
    HAVING COUNT(*) > 1) b
    ON a.ProductCode = b.ProductCode

    It displayed a list of Duplicates, so I then tried to enter this script which doesn’t seem to work at all, but it could be that it is because I don’t know how to combine the scripts to insert into the target table any productcode that doesn’t already exist and even if it is duplicated, I still need to bring it into the target table if it doesn’t exist once.

    insert dbo.tblInItem
    (ItemId,Descr,ProductLine,SalesCat,UomBase,UomDflt )
    select
    t1.ProductCode,
    t1.[Description],
    t1.'COFFEE',
    t1.'CS',
    t1.WeightMeasurement,
    t1.'EACH'
    from COFFEE.dbo.vueProductCase t1 left join dbo.tblInItem t2 on t1.ProductCode = t2.itemid
    where t2.itemid is null

    Can I get some help please??

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    cause my target table field ItemID can not be duplicated, also if the ItemID already exists, then I don’t want to import it either.
    Doesn't make sense.

    insert into the target table any productcode that doesn’t already exist and even if it is duplicated, I still need to bring it into the target table if it doesn’t exist once.
    Nope...this one doesn't make sense either.

    Give us a sample table create statement with insert statements for the data. Then, show us what you want the data to look like when complete. We should be able to help you pretty quickly then. Right now, someone else might be able to help you if they understand you. I'm not getting it though. []
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    My question is "What error message or incorrect results are you getting".

    Because I don't see anything syntactically wrong with your insert statement, and "It don't work fer nuffin at all" doesn't give us a lot of clues...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •