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.
Author |
Topic |
ss123
Starting Member
3 Posts |
Posted - 2011-12-14 : 10:14:08
|
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
52326 Posts |
Posted - 2011-12-14 : 10:21:41
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
ss123
Starting Member
3 Posts |
Posted - 2011-12-14 : 10:30:53
|
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. |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-14 : 10:40:18
|
Import into new, temporary-named, table.DROP old tableRENAME TempTable to original nameWon't work (easily) if you have foreign keys etc. on the original table though. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-12-14 : 12:17:41
|
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 |
|
|
ss123
Starting Member
3 Posts |
Posted - 2011-12-15 : 01:38:47
|
quote: Originally posted by Kristen Import into new, temporary-named, table.DROP old tableRENAME TempTable to original nameWon't work (easily) if you have foreign keys etc. on the original table though.
Thanks! That exactly what I was looking for! |
|
|
|
|
|