Solved

Transactions

Posted on 2014-10-13
3
77 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 45

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL LOOP CURSOR 3 40
Sql Stored Procedure 26 28
SQL Query to display duplicates ? 6 24
Convert SP in a format for debugging 7 11
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now