SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 delete and update every 2 minutes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ss123
Starting Member

Israel
3 Posts

Posted - 12/14/2011 :  10:14:08  Show Profile  Reply with Quote
Hi,

I have a folder with some data files that I scan (using c#.net) and insert into an sql table using bulk.
I need this to run every 2 minuets but first I have to delete what I have in my table and than add the new data I collected.

What is the best way for doing this in case I don't want the report that runs on this table to fail every time the table is deleting and updating ?

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/14/2011 :  10:21:41  Show Profile  Reply with Quote
usually we implement such automated processing using sql agent jobs. Inside job we will have steps to delete the existing data and then do scanning and population part. Once such a job is created, you add a schedule to it to execute every 2 minutes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ss123
Starting Member

Israel
3 Posts

Posted - 12/14/2011 :  10:30:53  Show Profile  Reply with Quote
Thanks, but my problem is not with scheduling this steps, but with the deleting part.

I'm afraid of the case where once I delete and someone will run the report it will get no rows.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/14/2011 :  10:40:18  Show Profile  Reply with Quote
Import into new, temporary-named, table.

DROP old table
RENAME TempTable to original name

Won't work (easily) if you have foreign keys etc. on the original table though.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 12/14/2011 :  12:17:41  Show Profile  Reply with Quote
Could you put a transaction around the DELETE/INSERT processing? I'm not sure how the Bulk insert impacts that.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith
Go to Top of Page

ss123
Starting Member

Israel
3 Posts

Posted - 12/15/2011 :  01:38:47  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

Import into new, temporary-named, table.

DROP old table
RENAME TempTable to original name

Won't work (easily) if you have foreign keys etc. on the original table though.



Thanks! That exactly what I was looking for!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000