Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    5

    Unanswered: Check for duplicate before insert with composite key

    Hi,
    I'm running into a problem with the following query:

    INSERT INTO NetScout.dbo.tb_Ref_App_Link(AppID,LinkID,Date,Vol ume)
    SELECT NetScout.dbo.tb_Application.AppID, NetScout.dbo.tb_Link.LinkID, Date, Volume
    FROM NetScout.dbo.tb_Application, NetScout.dbo.tb_Link, NetScout.dbo.[OLE DB Destination]
    WHERE NetScout.dbo.tb_Application.AppName = NetScout.dbo.[OLE DB Destination].AppName
    AND NetScout.dbo.tb_Link.LinkName = NetScout.dbo.[OLE DB Destination].LinkName
    AND NetScout.dbo.tb_Application.AppID
    NOT IN (SELECT NetScout.dbo.tb_Ref_App_Link.AppID
    FROM NetScout.dbo.tb_Ref_App_Link)
    AND NetScout.dbo.tb_Link.LinkID
    NOT IN (SELECT NetScout.dbo.tb_Ref_App_Link.LinkID
    FROM NetScout.dbo.tb_Ref_App_Link)
    AND Date
    NOT IN (SELECT Date
    FROM NetScout.dbo.tb_Ref_App_Link)


    I have a composite primary key in my "tb_Ref_App_Link" table, composed of LinkID, AppID, and Date. I want to check if the data i'm trying to insert already exists in the tb_Ref_App_Link table to avoid any referential integrity errors...
    How do I say: If LinkID AND AppID AND Date all appear on the same line, then don't insert it...

    Any help appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a UNIQUE constraint, then you won't have to check, the database will do it for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    5
    Ok.
    I added a Unique Index Constraint on the 3 columns and now it seems to work...
    Thanks!

Posting Permissions

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