CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: MSSQL INSERT Record If Not Exist

  1. #1
    Join Date
    Mar 2003
    Posts
    95

    MSSQL INSERT Record If Not Exist

    Table Layout:
    TABLE: dbo.Applications
    FIELDS: Date, Username, Application, Version

    I want to insert a record only if the record I'm about to insert does not match the Username and Application field.

    This is what I got so far...

    INSERT INTO dbo.Applications (Date, Username, Application, Version) VALUES('3/10/2009', 'Tom', 'Calculator', '2.0') WHERE NOT EXISTS (SELECT * FROM dbo.Applications WHERE Username = Tom AND Application = Calculator)
    Using VB .Net 2008 Express Edition

  2. #2
    PeejAvery's Avatar
    PeejAvery is offline Super Moderator Power Poster
    Join Date
    May 2002
    Posts
    10,943

    Re: MSSQL INSERT Record If Not Exist

    Have you tried using INSERT IGNORE?
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  3. #3
    Join Date
    Mar 2003
    Posts
    95

    Re: MSSQL INSERT Record If Not Exist

    It looks like every field has to match in order for it to ignore it. In this case the Date field will always be different, I just want to compare the username and application field, if those already exist then ignore it.

    lets say
    03/12/2009 Tom Calc 2.0
    02/12/2008 Tam Calc 2.0
    01/13/2003 Tqm Calc 2.0

    If I try to Insert 09/09/2009 Tom Calc 3.0
    It will ignore it because Tom and Calc already exist, even though it's a different date and a different version
    Using VB .Net 2008 Express Edition

  4. #4
    Join Date
    Feb 2005
    Location
    Denmark
    Posts
    742

    Re: MSSQL INSERT Record If Not Exist

    Use IF to check if something exists (or not), and then act accordingly:

    Code:
    IF NOT EXISTS (SELECT * FROM dbo.Applications WHERE Username = Tom ANDApplication = Calculator) BEGIN
     INSERT INTO dbo.Applications 
     (Date, Username, Application, Version) 
     VALUES
     ('3/10/2009', 'Tom', 'Calculator', '2.0') 
    END

  5. #5
    Join Date
    Mar 2009
    Posts
    1

    Thumbs up Re: MSSQL INSERT Record If Not Exist

    Here is a solution with out the begin and end statements.

    INSERT INTO dbo.Applications (Date, Username, Application, Version)
    select '3/10/2009', 'Tom', 'Calculator', '2.0'
    WHERE NOT EXISTS (SELECT *
    FROM dbo.Applications
    WHERE Username = Tom
    AND Application = Calculator)

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)