SQL Server Performance Forum – Threads Archive
insert or update
I’m trying to use the DTS export functionality with a script to transfer data from one db to another. The issue i’m having is that I don’t have just inserts or updates, i have both. Is there a fast way to do this? If the records in the target table, then update it, if not insert it? For an example of what i’m trying to do, look at my last post: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=13292 Thanks, BenWhy can’t you use Replication in this case? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
The target db is an archive db and is supposed to keep records much longer than the production db. Therefore, i don’t want records that get deleted out of the prod db to be deleted out of the archive db. Thanks, Ben
Update T
set col=S.col,….
from TargetTable T inner join SourceTable S
on T.keycol=S.keycol If @@RowCount=0
Insert into TargetTable ………. Madhivanan Failing to plan is Planning to fail
that’s what i was looking for. How will that work though with a multitude of transactions? Will it try to do all the updates, then do the inserts? or will it keep iterating through one at a time? I’m doing stuff at the beginning of the script that i only want to run once, and i need all of the rows copied for previous tables so that fk constraints aren’t violated. here is the start to my script. declare @newdatetimedt datetime
declare @datetimedt datetime
declare @datetimevar varchar(20)
declare @year varchar(4)
declare @month varchar(3)
declare @day varchar(3)
declare @hour varchar(4)
declare @minute varchar(4)
declare @second varchar(4)
declare @ms varchar(5) set @newdatetimedt = getdate()
set @datetimedt = (select datetimedatetime from cryrep.dbo.datetimedts where row = 1)
set @datetimedt = dateadd(minute,-5,@datetimedt)
set @year = cast(datepart(yyyy,@datetimedt) as varchar(4))
set @month = right(‘0’ + cast(datepart(mm,@datetimedt) as varchar(2)),2)
set @day = right(‘0’ + cast(datepart(dd,@datetimedt) as varchar(2)),2)
set @hour = right(’00’ + cast(datepart(hh,@datetimedt) as varchar(2)),2)
set @minute = right(’00’ + cast(datepart(mi,@datetimedt) as varchar(2)),2)
set @second = right(’00’ + cast(datepart(ss,@datetimedt) as varchar(2)),2)
set @ms = right(’00’ + cast(datepart(ms,@datetimedt) as varchar(3)),2)
set @datetimevar = @year + @month + @day + @hour + @minute + @second + @ms update cryrep.dbo.datetimedts
set datetimedatetime = @newdatetimedt
where row = 1 /* these last lines will hopefully be replaced with what you had suggested, but i only want the above to run once per time this script is called.*/ insert into cryrep.dbo.loctype select * from boscov.dbo.loctype
insert into cryrep.dbo.location select * from boscov.dbo.location Loc where Loc.dateTimeStamp > @dateTimevar
insert into cryrep.dbo.carton select * from boscov.dbo.carton Carton where Carton.dateTimelasttrans > @dateTimedt Thanks, Ben
All right, i’m getting closer. Now i just need help figuring out how to do something to update and insert. I have this right now: if exists (select * from cryrep.dbo.locationstatus inner join boscov.dbo.locationstatus on cryrep.dbo.locationstatus.locationstatusid = boscov.dbo.locationstatus.locationstatusid)
begin
update cryrep.dbo.locationstatus
set cryrep.dbo.locationstatus.locationstatusid = boscov.dbo.locationstatus.locationstatusid, cryrep.dbo.locationstatus.description = boscov.dbo.locationstatus.description
from cryrep.dbo.locationstatus inner join boscov.dbo.locationstatus on cryrep.dbo.locationstatus.locationstatusid = boscov.dbo.locationstatus.locationstatusid
end
else if not exists (select * from cryrep.dbo.locationstatus inner join boscov.dbo.locationstatus on cryrep.dbo.locationstatus.locationstatusid = boscov.dbo.locationstatus.locationstatusid)
begin
insert into cryrep.dbo.locationstatus select * from boscov.dbo.locationstatus
end The issue with that is that once the if is true for one file, the insert never runs. I want it to update the rows that are already there, and then insert the rows that aren’t. I can’t figure out how to make it do that. Is there some sort of command like "post" that SQL does automatically (tries to insert and if it fails, tries to update)? Thank you for your time, Ben
try to think about logic which madhivanan has suggested.
first run update statement. it will update only when records are found and increments @@rowcount else keep it as 0.
So after update check for @@rowcount for 0 and if it is that means no records are updated so its time to insert those records.
The general idea for an insert-update is to use two queries in succession: first an update for all matching rows, then an insert for all new rows. You don’t really need to use an IF / ELSE construct, it only confuses the issue. UPDATE tblTarget
SET col = ……..
FROM tblTarget INNER JOIN tblSource
ON tblTarget.key = tblSource.Key INSERT INTO tblTarget …………
SELECT …………
FROM tblSource LEFT JOIN tblTarget
ON tblSource.key = tblTarget.key
WHERE tblTarget.key IS NULL If you would start by inserting the new rows, then the update of matching rows takes more time – because the "new" rows will then also be matching rows. So do the update first, then the insert.
I understand that…i think. The only problem with it is if it updates some records, then the rowcount is not zero anymore and it won’t insert the records that need to be inserted. This query needs to both update and insert ones that aren’t there at the same time. so say i have a table with 5 records in the source. 4 of them are in the target. One record that is in both has been changed in the source and needs to be updated in the target. Also there is one in the source that is not yet in the target so it needs to be inserted. In this case, it seems that the one that needs to be inserted, won’t be because the row count is no longer zero due to the one update. Am i correct on this thinking? Thanks, Ben
oops, i replied before seeing adriaan’s post. I will try that. It appears that it will work. Thank you very much. Ben
That worked. Thank you so much, Ben
]]>