July 5, 2016 at 3:22 am
Hi ,
I'm currently trying to create a insert statement that will only insert values if the value doesn't not exist
which in my case is the [name] column, so if the name already exists do not insert.
Currently ive mocked up something like this, but this doesn't allow me to run script at once, its expecting the values to be passed through manually... with the @name and @val
declare
@name AS VARCHAR(500),
@val AS VARCHAR(500)
set @name = 'Manual'
SET @val = '-53'
IF EXISTS(SELECT 'True' FROM u_apppreferences WHERE name = @name)
BEGIN
--Record Exists
SELECT 'This record already exists!'
END
ELSE
BEGIN
--This means the record isn't in there already, let's go ahead and add it
SELECT 'Record Added'
INSERT into u_apppreferences(name, Val) VALUES(@name, @val)
END
the goal is to remove the parameters and just let the script run itself and pick up where the name is missing....
any ideas?
July 5, 2016 at 3:45 am
INSERT into u_apppreferences(name, Val)
SELECT @name, @val
WHERE NOT EXISTS (SELECT 1 FROM u_apppreferences WHERE name = @name)
as your current one is prone to concurrency problems (will cause duplicates under load)
http://source.entelect.co.za/why-is-this-upsert-code-broken
the goal is to remove the parameters and just let the script run itself and pick up where the name is missing....
Where would the script get the names it needs to check, and where would it get the values to insert?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2016 at 7:35 am
the values from the other table which exists on a different database
July 6, 2016 at 7:57 am
In that case, instead of SELECT @name, @val WHERE, it would be
SELECT s.name, s.val
FROM SomeOtherDatabase.dbo.SomeOtherTable s
WHERE NOT EXISTS (SELECT 1 FROM u_apppreferences WHERE name = s.name)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2016 at 8:10 am
For key value pairs, this is typically what you want. If name exists (@@rowcount = 1), then it's value is updated. If name doesn't exist (@@rowcount = 0), then it is inserted in a subsequent statement.
update u_apppreferences
set Val = @val
where name = @name;
if @@rowcount = 0
insert into u_apppreferences ( name, Val )
values ( @name, @val );
Collisions can be mitigated by implementing ignore_dup_key option on the primary key.
create table u_apppreferences
(
name varchar(20) not null
primary key with (ignore_dup_key = on),
Val varchar(20) not null
);
In any event, you'll most likely want name as the primary key.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 6, 2016 at 11:58 am
USE dba
GO
CREATE TABLE u_apppreferences (
NAME VARCHAR(500)
,val VARCHAR(500)
);
DECLARE @name AS VARCHAR(500),@VAL AS VARCHAR(500);
SET @name = 'Manual';
SET @val = '-53';
IF NOT EXISTS (
SELECT 1
FROM u_apppreferences
WHERE NAME = @name
AND val = @val
)
BEGIN
INSERT INTO u_apppreferences (NAME,Val)
VALUES (@name,@VAL)
END
ELSE
PRINT 'Name and Val exists';
July 6, 2016 at 1:17 pm
sunita2912 (7/6/2016)
IF NOT EXISTS (SELECT 1
FROM u_apppreferences
WHERE NAME = @name
AND val = @val
)
BEGIN
INSERT INTO u_apppreferences (NAME,Val)
VALUES (@name,@VAL)
END
ELSE
PRINT 'Name and Val exists';
As with the OP's original code, that is prone to concurrency problems (will cause duplicates under load)
http://source.entelect.co.za/why-is-this-upsert-code-broken
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2016 at 7:55 am
Eric M Russell (7/6/2016)
For key value pairs, this is typically what you want. If name exists (@@rowcount = 1), then it's value is updated. If name doesn't exist (@@rowcount = 0), then it is inserted in a subsequent statement.
update u_apppreferencesset Val = @val
where name = @name;
if @@rowcount = 0
insert into u_apppreferences ( name, Val )
values ( @name, @val );
Collisions can be mitigated by implementing ignore_dup_key option on the primary key.
create table u_apppreferences(
name varchar(20) not null
primary key with (ignore_dup_key = on),
Val varchar(20) not null
);
In any event, you'll most likely want name as the primary key.
I'm just wondering if your code might also contain the same sort of race condition pointed out by Gilamonster?
July 7, 2016 at 8:10 am
patrickmcginnis59 10839 (7/7/2016)
I'm just wondering if your code might also contain the same sort of race condition pointed out by Gilamonster?
Yes it can.
Two sessions start, one runs the update, it affects no rows. Second one runs the update as soon as it can get locks, it also affects no rows, first one inserts, second one inserts.
Adding the ignore_dup_key will ensure no errors are thrown, but still two different sessions will think they inserted a row.
With that structure, should be enough to just wrap the two in a transaction and switch the isolation level to serialisable for the duration. Locking hints shouldn't be necessary
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2016 at 8:40 am
Gail Shaw - As with the OP's original code, that is prone to concurrency problems (will cause duplicates under load)
Sipping from the hydrant ... I doubt I would have been able to find this excellent article of yours in the T-SQL ocean. It was my luck you pointed to it while answering an (apparently) simple INSERT question.
July 7, 2016 at 8:48 am
You might want to look at using a global table for this work.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply