NEWBIE: Can you insert with automatic "overwrite"

Dear Experts,

Is it possible to insert a record into a keyed table such that you automatically replace the record if it already exists?  Or do you have to delete it first, and then insert it?  If it can be done, what is the syntax?

Thanks!
--BrianMc1958

p.s. I'm on MS SQL Server.
BrianMc1958Asked:
Who is Participating?
 
BillAn1Connect With a Mentor Commented:
By default, when you do an insert, you will get an error if the row already exists, and you need to handle this yourself.
A way round this can be to create a trigger on your table. This will then intercept the inserts, and do the checking for you, converting the inserts to updates for rows which already exist, and only inserting new rows.

if you do something like this : (this assumes your table is called MyTable, the key column is col1, and the other cols that exist are called col2, col3, col4 - adjust accordingly.)

create trigger MyTrigger on MyTable
instead of insert
as
begin
       update MyTable
       set col2 = i.col2,
            col3 = i.col3,
           col4 = i.col4
         from inserted i where MyTable.col1 = i.col1

         insert into MyTable
         select * from inserted
         where col1 not in (select col1 from MyTable)
end


once you have created the tirgger, you can happily do an insert into the table - the trigger will convert it into an update if the key already exists.
0
 
Kevin3NFCommented:
No....you need to either update the exisitng record or delte and insert.
0
 
BrianMc1958Author Commented:
Sorry.  I should have been more specific.  I won't know if the record already exists or not.  So I can't do update, can I?

Thanks again,
BrianMc1958
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Kevin3NFConnect With a Mentor Commented:
In your stored procedure, you can check for existence of the record:


Use Northwind
Go

if exists (select * from customers where companyname like 'b%')
    Begin
        Print 'There is a company that starts with B'
    End
Else
    Begin
        Print 'No companies start with b'
    End


of course, you would substitute in an Update or Delete/Insert and some transaction logic in the appropriate places
0
 
jrb1Connect With a Mentor Commented:
There are 2 ways to handle it, and the best depends on the normal case.  If the insert will usually be successful, then

insert row...
if dup row error
   update row....
end if

If there will usually be a row in the DB, then I would

update row...
if not found
   insert row
end if

I just do this to reduce the amount of SQL on the DB.  Of course, you could also do as the prior post said:

check for a row
if row exists
   update row
else
   insert row
end if
0
 
BrianMc1958Author Commented:
Thanks, Kevin.  I'm going to re-post the question a little more clearly.
0
 
Kevin3NFCommented:
Why re-post?  Just clarify here, and we'll save you some points
0
 
BrianMc1958Author Commented:
Thank you, everybody.  This helps a lot.  Although all three of you were equally helpful, I need to pick one as the accepted answer, and here I'll take the most ambitious (for a newbie) solution.

Thanks again!

--BrianMc1958
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.