Insert with exists

  • How do you do an insert w/ exists?

    I only want to insert into the table if the value does not exist.

    Table TableA

    Values (user_id, fname, lname)

    insert into TableA

    (user_id, fname, lname)

    select 'ajones','ANN','JONES'

    from TableA

    WHERE not exists (select * from TableA Where user_id = 'ajones')

    This won't work - how do I do it?

  • Couple questions, are you inserting scalar, specific data? Is there a PK on the fields?

    If there's a primary key, and there should be, then it won't insert. You could move the check up

    if not exists( select xxx ...)

    insert into Mytable

    ....

    Is the current data in a table? from your example it doesn't seem to be. If you're inserting into TableA, where does it come from?

  • Rog - where are your values coming from (The ones you wish to insert)? A table?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • There is a primary key on this table - user_id.

    I was wondering if I could do this in a single select - i.e. not using an if statement.

    The values are hard-coded OR are variables coming from somewhere besides a table.

    Thank you

  • I think you can use:

    insert into TableA

    (user_id, fname, lname)

    select 'ajones','ANN','JONES'

    Where user_id <> 'ajones'

    (Something of a moot no-op statement, but I assume your requirements have been simplified.)

    or use an EXCEPT statement if the criteria is a bit more complex.

    or use a WHERE NOT EXISTS (select user-id from tableA where user-id = 'ajones')

    In MS Access, the "wizard" way of finding duplicates (and avoiding extranious insertions)was to use a subquery of the form WHERE user_id not in (select user_ID from TableA) which was typically slow. At least in that environment, it was always faster to do a left outer join from the input table to the output table (TableA), and then simply, in the where statement, test for nulls in the output table primary key to see what was missing and should be inserted.

  • The above mentioned method will work but if you need to update the existing record if it exist with new value and insert if it does not, use the UPSERT method.

    UPDATE dbo.tbTable

    SET Column1 = @Parm1

    , Column2 = @Parm2

    WHERE PrimaryKeyColumn = @Parm3

    IF @@rowcount = 0

    INSERT dbo.tbTable

    ( PrimaryKeyColumn

    , Column1

    , Column2 )

    VALUES

    ( @parm3

    , @parm1

    , @parm2)

    -Roy

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply