Solved

Transactions

Posted on 2014-10-13
3
82 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 49

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

696 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