?
Solved

Transactions

Posted on 2014-10-13
3
Medium Priority
?
84 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 51

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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 …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 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