insert new update existing statement | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

insert new update existing statement

I am trying to write a statement that will insert new records and update existing (based on the existence of a compoun primary key (sys, worktype, unitcd)) in a table (worktypecategories) The statement below seems to work in my tests so far. I’m not sure if there is an easier or more effecient way of accomplishing this. Any advice would be greatly appreciated. Thanks,
Matt declare @sys varchar(10)
declare @worktype varchar(10)
declare @unitcd varchar(10)
declare @category varchar(10) set @sys = ‘Sys1’
set @worktype = ‘myWorkType’
set @unitcd = ‘myUnit’
set @category = ‘myCat’ if(select count(*)
from worktypecategories
where sys = @sys and
worktype = @worktype and
unitcode = @unitcd)> 0
update worktypecategories
set categorycode = @category
where sys = @sys
and worktype = @worktype
and unitcode = @unitcd
else
insert into worktypecategories
values (@sys, @worktype, @category, @unitcd)
General approach Update t1
set col1=t2.col1,……
from table1 t1 inner join table2 t2 on t1.keycol=t2.keycol Insert into table1
Select columns from table2 t2
where not exists(select * from table1 where keycol=t2.keycol) Madhivanan Failing to plan is Planning to fail
If you’re handling one record at a time, this would work. Is (sys, worktype, unitcode) a unique key to your worktypecategories table? If it is NOT a unique key, then you may get some improvement by doing:
IF EXISTS (SELECT * FROM … WHERE …) … instead of:
IF (SELECT COUNT(*) FROM … WHERE …) > 0 If you need to handle multiple records, with a staging table, then in your given script you would have to open a cursor for the staging table, then repeat your update/insert statement. In that scenario, you will get better performance by using a set-based approach, with two queries in a batch: UPDATE w
SET w.categorycode = t.categorycode
FROM worktypecategories w
INNER JOIN stagingtable t
ON w.sys = t.sys AND w.worktype = t.worktype AND w.unitcode = t.unitcode INSERT INTO worktypecategories (categorycode, sys, worktype, unitcode)
SELECT t.categorycode, t.sys, t.worktype, t.unitcode
FROM stagingtable t
LEFT JOIN worktypecategories w
ON t.sys = w.sys AND t.worktype = w.worktype AND t.unitcode = w.unitcode
WHERE w.sys IS NULL Always do the update before the insert, to avoid unnecessary updates, which will only take up valuable time.
Adriaan, Thanks for the reply. Yes, sys, worktype, unitcode is the primary key for this table. What I am doing is looping through a Java resultset that is the result of a query from a DB2 table. So I am handling them one at a time. Thanks again for the advice.
]]>