Combine select then update or insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combine select then update or insert

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-20 : 11:09:04

Trying to do following:

Select from a db2 table and if document (bol_number = vdoc) exists perform an update, otherwise do an insert:

if(exists      (select 
                     vcdoc, 
                     DATEADD(DAY, vcdgj % 1000, DATEADD(YEAR, vcdgj / 1000, -1)),
                     vcprd1,
                     substring(vcpnam,1,30) as vcpnam,
                     cast(vcqty as decimal(15,2)) / 100,
                     cast(vcnetg as decimal (15,2)) / 100
               from  AS400SRV_IBMDARLA.VGSYS400.VGITFRLIB.F55124LA
               where vcpltc = @Terminal and
		             (@JdeProduct = ' ' or vcprd1 = @JdeProduct) and 
                     (@JdeDateFrom = 0 or vcdgj >= @JdeDateFrom) and
                     (@JdeDateTo = 0 or vcdgj <= @JdeDateTo)))
begin
               update #Combined_DisbursementTable 
               set    jde_gross_qty = vcqty,
                      jde_net_qty   = vcnetg,
               where  #Combined_DisbursementTable.bol_number = vcdoc 
end
else
begin
insert into    #Combined_DisbursementTable(
                                            bol_number, 
                                            trans_date, 
                                            product_code,
                                            product_name,
                                            jde_gross_qty, 
                                            jde_net_qty)
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 11:17:51

I would do this in more steps, since utilizing a linked server costs on network traffic.

SELECT ...
INSERT INTO #Stage
FROM AS400SRV_IBMDARLA.VGSYS400.VGITFRLIB.F55124LA

-- update existing records
UPDATE dt
SET dt.jde... ,
dt.jde...
FROM #Combined_DisbursementTable AS dt
inner join #stage aS s on s.vcdoc = dt.bol_number

-- insert new records
insert #Combined_DisbursementTable ( , , , , )
SELECT s.vcdos as bol_number,
s.trans_date,
...
from #Stage as s
where not exists (Select * from #Combined_DisbursementTable AS ct WHERE ct.bol_number = s.vcdoc)

drop table #stage

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 11:18:21

Have you migrated to SQL Server 2008 yet?
If so, have a look at the MERGE command.

N 56°04'39.26"
E 12°55'05.63"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-20 : 11:31:00

I'm already doing it in 3 steps just as you described. Reason I was looking at the "all in one" approach is that current response time for the initial select is slow.

Have not migrated to 2008 yet but I will look at the "merge" command.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources