INSERT or CREATE record in SQL Server database

1145610 pts.
Tags:
Database
SQL Server
I have this SQL Server table structure:
MyTable(KEY, datafield1, datafield2...)
And now I have to update an existing record / create a new one if it doesn't appear. What's the best way to do that?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.
You can create a procedure. 
When you execute the procedure, you will provide the value of a parameter to be passed to the procedure. 
The procedure will check the table data for a row with a field matching the value of the parameter you just provided. 
If that row exists, it will run an UPDATE statement.
If not, it will run an INSERT statement. 
example for syntax purposes: 
CREATE PROCEDURE ProcedureName (@parameter_01 AS datatype) 
AS
IF EXISTS(SELECT * FROM Table WHERE datafield_n = @parameter_01) 
UPDATE Table SET … WHERE datafield_n = @parameter_01
ELSE INSERT INTO Table … 
GO

EXEC ProcedureName parameter_value

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    What have you tried so far? Is this do be done as a single statement or a stored procedure?
    133,595 pointsBadges:
    report
  • carlosdl
    In general, you would have to try one of the operations, and if it fails, then try the other (for example, trying the insert first, and if it fails with a duplicate key error, then try the update, or vice versa.).  You should probably decide which one to try first, depending on the probability of them failing.
    84,805 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: