Assume a table structure of MyTable(KEY, datafield1, datafield2...)
Often I want to either update an existing record, or insert a new record if it doesn't exist.
essentially if (key exists) Run Update command ELSE run insert command
What's the best performing way to write this?
IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)
UPDATE [Table] SET propertyOne = propOne, property2 . . .
ELSE
INSERT INTO [Table] (propOne, propTwo . . .)
Edit:
Alas, even to my own detriment, I must admit the solutions that do this w/o a select seem to be better since they accomplish the task with one less step.
Do an UPSERT:
UPDATE MyTable SET FieldA=@FieldA WHERE Key=@Key IF @@ROWCOUNT = 0 INSERT INTO MyTable (FieldA) VALUES (@FieldA)
Guys, don't forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous.
When multiple threads will try to perform Insert-or-update you can easily
get primary key violation.
Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone.
To avoid deadlocks and PK violations you can use something like this:
begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
update table set ...
where key = @key
end
else
begin
insert table (key, ...)
values (@key, ...)
end
commit tran
or
begin tran
update table with (serializable) set ...
where kay = @key
if @@rowcount = 0
begin
insert table (key, ...) values (@key,..)
end
commit tran
Doing an if exists ... else ... involves doing two requests minimum (one to check, one to take action). The following approach requires only one where the record exists, two if an insert is required:
DECLARE @RowExists bit
SET @RowExists = 0
UPDATE MyTable SET DataField1 = 'xxx', @RowExists = 1 WHERE Key = 123
IF @RowExists = 0
INSERT INTO MyTable (Key, DataField1) VALUES (123, 'xxx')
I usually do what several of the other posters have said with regard to checking for it existing first and then doing whatever the correct path is. One thing you should remember when doing this is that the execution plan cached by sql could be nonoptimal for one path or the other. I believe the best way to do this is to call two different stored procedures.
FirstSP: If Exists Call SecondSP (UpdateProc) Else Call ThirdSP (InsertProc)
Now, I don't follow my own advice very often, so take it with a grain of salt.
If you use ADO.NET, the DataAdapter handles this.
If you want to handle it yourself, this is the way:
Make sure there is a primary key constraint on your key column.
Then you:
You can also do it the other way round, i.e. do the insert first, and do the update if the insert fails. Normally the first way is better, because updates are done more often than inserts.
If you want to UPSERT more than one record at a time you can use the ANSI SQL:2003 DML statement MERGE.
MERGE INTO table_name USING table_name ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])
Check out Mimicking MERGE Statement in SQL Server 2005.
MS SQL Server 2008 introduces the MERGE statement, which I believe is part of the SQL:2003 standard. As many have shown it is not a big deal to handle one row cases, but when dealing with large datasets, one needs a cursor, with all the performance problems that come along. The MERGE statement will be much welcomed addition when dealing with large datasets.
See my detailed answer to a very similar previous question
@Beau Crawford's is a good way in SQL 2005 and below, though if you're granting rep it should go to the first guy to SO it. The only problem is that for inserts it's still two IO operations.
MS Sql2008 introduces merge from the SQL:2003 standard:
merge into tablename
where idfield = 7
when matched then
update
set field1 = 'new value',
field2 = 'different value',
...
when not matched then
insert ( idfield, field1, field2, ... )
values ( 7, 'value one', 'another value', ... )
Now it's really just one IO operation, but awful code :-(
i found this code in the postgreSQL documentation.
BEGIN;
-- other operations
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Assume the above fails because of a unique key violation,
-- so now we issue these commands:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
-- continue with other operations, and eventually
COMMIT;
i thought it was very valuable for PostgreSQL users
Although its pretty late to comment on this I want to add a more complete example using MERGE.
Such Insert+Update statements are usually called "Upsert" statements and can be implemented using MERGE in SQL Server.
A very good example is given here: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
The above explains locking and concurrency scenarios as well.
I will be quoting the same for reference:
ALTER PROCEDURE dbo.Merge_Foo2
@ID int
AS
SET NOCOUNT, XACT_ABORT ON;
MERGE dbo.Foo2 WITH (HOLDLOCK) AS f
USING (SELECT @ID AS ID) AS new_foo
ON f.ID = new_foo.ID
WHEN MATCHED THEN
UPDATE
SET f.UpdateSpid = @@SPID,
UpdateTime = SYSDATETIME()
WHEN NOT MATCHED THEN
INSERT
(
ID,
InsertSpid,
InsertTime
)
VALUES
(
new_foo.ID,
@@SPID,
SYSDATETIME()
);
RETURN @@ERROR;
Before everyone jumps to HOLDLOCK-s out of fear from these nafarious users running your sprocs directly :-) let me point out that you have to guarantee uniqueness of new PK-s by design (identity keys, sequence generators in Oracle, unique indexes for external ID-s, queries covered by indexes). That's the alpha and omega of the issue. If you don't have that, no HOLDLOCK-s of the universe are going to save you and if you do have that then you don't need anything beyond UPDLOCK on the first select (or to use update first).
Sprocs normally run under very controlled conditions and with the assumption of a trusted caller (mid tier). Meaning that if a simple upsert pattern (update+insert or merge) ever sees duplicate PK that means a bug in your mid-tier or table design and it's good that SQL will yell a fault in such case and reject the record. Placing a HOLDLOCK in this case equals eating exceptions and taking in potentially faulty data, besides reducing your perf.
Having said that, Using MERGE, or UPDATE then INSERT is easier on your server and less error prone since you don't have to remember to add (UPDLOCK) to first select. Also, if you are doing inserts/updates in small batches you need to know your data in order to decide whether a transaction is appropriate or not. It it's just a collection of unrelated records then additional "enveloping" transaction will be detrimental.
INSERT INTO Sales(TransId, Status, Amount) VALUES(123, 'Pending', 20) ON DUPLICATE KEY UPDATE Status = 'Paid'
Discription about following Sp:-(I know its too late to answer but plz check the following approach)
It lets the user decide to update or insert. if user inter @id = 0 he is callinf this Sp to insert and if @id = 0 = non zero then he is willing to update..
ALTER procedure [dbo].[USP_Insert_Update_User](@Id int,@chvEndUserTypeId varchar(3),@chvName varchar(50), @chvPhone1 varchar(14),@chvPhone2 varchar(14),@chvMobile1 varchar(14),@chvMobile2 varchar(14),@chvAddress varchar(500),@chvEmailId varchar(50),@intSchoolId int,@IsActive int,@intout int output) as
Begin
If(@Id = 0)
if exists(select EmailId from TblUser where( Mobile1 = @chvMobile1 and SchoolId = @intSchoolId) or (SchoolId = @intSchoolId and UserTypeId = 'A') )
set @intout =-1
else
begin
begin try
INSERT INTO SBTS_TestingDB.[dbo].[TblUser]
([UserTypeId]
,[Name]
,[Phone1]
,[Phone2]
,[Mobile1]
,mobile2
,[Address]
,[EmailId]
,[SchoolId]
,Password,IsActive )---0=inactive;1=active
VALUES(@chvEndUserTypeId,@chvName,@chvPhone1,@chvPhone2,@chvMobile1,@chvMobile2,@chvAddress,@chvEmailId,@intSchoolId,@chvMobile1,@IsActive)
set @intout=SCOPE_IDENTITY()
end try
begin catch
set @intout=-2 -- not insertd
end catch
end
else
begin
begin try
UPDATE SBTS_TestingDB.[dbo].[TblUser]
SET [Name] = @chvName
,[Phone1] = @chvPhone1
,[Phone2] = @chvPhone2
,[Mobile1] = @chvMobile1
,[Mobile2] = @chvMobile2
,[Address] = @chvAddress
,[EmailId] = @chvEmailId
,Isactive = @IsActive
WHERE Id = @Id
set @intout = @Id --No need to update CodeIf
end try
begin catch
set @intout = -3 --neot updated
end catch
end
End