NEWBIE: Can you insert with automatic "overwrite"

Posted on 2005-05-11
Medium Priority
Last Modified: 2012-08-14
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?


p.s. I'm on MS SQL Server.
Question by:BrianMc1958
LVL 21

Expert Comment

ID: 13980310
No....you need to either update the exisitng record or delte and insert.

Author Comment

ID: 13980356
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,
LVL 21

Assisted Solution

Kevin3NF earned 200 total points
ID: 13980474
In your stored procedure, you can check for existence of the record:

Use Northwind

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

of course, you would substitute in an Update or Delete/Insert and some transaction logic in the appropriate places
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

LVL 25

Assisted Solution

jrb1 earned 200 total points
ID: 13980495
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
   insert row
end if

Author Comment

ID: 13980511
Thanks, Kevin.  I'm going to re-post the question a little more clearly.
LVL 21

Expert Comment

ID: 13980620
Why re-post?  Just clarify here, and we'll save you some points
LVL 17

Accepted Solution

BillAn1 earned 200 total points
ID: 13980947
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
       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)

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.

Author Comment

ID: 13985814
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!


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question