December 17, 2007 at 12:30 pm
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?
December 17, 2007 at 12:55 pm
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?
December 17, 2007 at 12:59 pm
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?
December 17, 2007 at 1:12 pm
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
December 17, 2007 at 1:18 pm
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.
December 17, 2007 at 1:29 pm
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