May 27, 2009 at 4:52 am
Hi,
Problem.
i have huge production database table and i want to archive some data to archival server table.
but the issue is while i am trying to insert data from prod to archival table its failing , because some of the old data is already exist on archival database table.
Request.
i want to make a insert statement that should check data existence on archival server before execution of insert statement.
if data is still there that match with prod server table first should delete and insert a fresh data from prod.
pls give me the solution
i will be very thankful to you.
Regards
Syed Naveed
May 27, 2009 at 5:22 am
Hi Syed
How huge are we talking here, in the case you have mentioned,how about using a left outer join on a primary key on both tables, or delete the common rows and then try to insert.
Hope this helps 🙂
May 27, 2009 at 5:47 am
syed muhammad naveed (5/27/2009)
i want to make a insert statement that should check data existence on archival server before execution of insert statement.if data is still there that match with prod server table first should delete and insert a fresh data from prod.
Hi. Before inserting new lines into the archive table you must remove all lines in archive matching lines in production data :
delete ARCHIVE
where KEYFIELD in (select KEYFIELD from PRODTABLE)
if you have more than one field in primary key:
delete ARCHIVE
from ARCHIVE inner join PRODTABLE on ARCHIVE.key1 = PRODTABLE.ke1 and ARCHIVE.key2 = PRODTABLE.key2
ciao
Giacomo
May 27, 2009 at 6:55 am
I know this might be a trivial question, but it's worth asking... is SQL Server a 2005 version?
If you are lucky enought to have a SQL 2008 environment, I reckon you could use the MERGE statement ( example here: http://blogs.techrepublic.com.com/datacenter/?p=194 )
Any particular reason why you have to do it in one statement? otherwise I'd recommend a stored procedure for several reasons (ACID among all)?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply