?
Solved

Transactions

Posted on 2014-10-13
3
Medium Priority
?
88 Views
Last Modified: 2014-10-28
Hello,

I have table that contains data that is updated daily.  Each night a job runs that truncates the table and then inserts a new set of values.

The insert statement takes a few hours to execute.

At the moment the table is empty for those few hours.  Other applications that run during the day select data from the table - so its important that everything is completed during the night.

I was wondering what would be the effect of putting everything in a transaction.    Would the table appear to contain data all the time, or would it be empty during the period the transaction was commited?

Is this a good idea?

A second thought would be to use merge.   Would the table always appear to contain data. Is this a good idea?

/richard
0
Comment
Question by:soozh
3 Comments
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40377213
Did you try BULK INSERT? It's a faster way to insert data in tables.
0
 
LVL 5

Assisted Solution

by:Bob Bender
Bob Bender earned 1000 total points
ID: 40377400
Transactions thru SQL will not show up in the database until they is a COMMIT statement done.

You can load all the new entries, then commit the transaction to make them appear in your DB all at once.  Essentially, you are writing all databse entries for a task and using the transaction as a wrapper for the whole process.

Transactions allow you to maintain database "referential integrity).   Like using an Orders and Orders_Detail  table.
One relies on the other, and makes sure you db is linked properly.   If there is a problem during updates, all db activity during the transaction is rolled out.  not leaving lost entries, and having to figure them out yourself.

Bob
0
 
LVL 18

Accepted Solution

by:
lludden earned 1000 total points
ID: 40377457
Committing a transaction isn't instantaneous.  A better way might be to insert the data into a new table, then when the data is all inserted, drop the old table and rename the new table to the  name of the original table.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question