Solved

Transactions

Posted on 2014-10-13
3
80 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 47

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 250 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 250 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

832 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