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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 ETL Batch Integrity Puzzle
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

10 Posts

Posted - 02/05/2013 :  14:14:50  Show Profile  Reply with Quote
We don't have a EDW or data staging environment yet but have data bridges in place to get three tables amounting to 1.5GB of data(8 million rows) and need to move them to a co location according to our SLA. We have 2 hours and 24 minutes on Saturday or Sunday.

Assuming I move the backups across the network and restore, now I have the source data on the production target. What is the best strategy to update the target tables whith batch integrity in mind?
Start a transaction, delete the target tables,then insert.... commit.
If this take 60 minutes what will be the user experience for the case where someone starte browsing data before I started my deletes?

this is a very basic RDBMS question I feel.
User of a web app connects to SQL and browses data from three tables. My ETL transaction deletes the tables and Inserts new data. does the user have access to the 'pre deleted' snapshot of the tables or do any new reads need to wait until the ETL load is done?

I hope this makes sense and appreciate any insight.

Embarcadero/SQL Server/and now... Informatica

Posting Yak Master

160 Posts

Posted - 02/05/2013 :  23:22:27  Show Profile  Reply with Quote
If a user starts browsing data before your ETL load, he will have the data in the session id he opened. For e.g. let me say, user fired select * from xyz table and got the result set as 250 rows. Your ETL load finished downloading data (Delete and Insert) which has 350 rows now. If he fires the same select statement it would show the updated records with 350.

If user is firing queries at the time of your ETL load, he will get the result with most updated records.

Hope this helps.
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 02/06/2013 :  00:13:06  Show Profile  Reply with Quote
you may be better off giving the user a read only snapshot whilst you do the delete insert on background. This will not have any downtime on user accessibility of the data. at the end of process your updated data would be available to users as well.

SQL Server MVP

Go to Top of Page
  Previous Topic Topic Next 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.02 seconds. Powered By: Snitz Forums 2000