49196

Most efficient way to move table rows from one table to another

I've created a denormalized table that needs to be inserted/updated every hour. The process is rather involved from a data perspective, so i am looking for a recommend way to update the table without disrupting the users.

i am thinking of having a separate table that my process inserts/updates to and once complete, need a way to push those changes to my live production table.

any help would be great!

Answer1:

Another solution is to use multiple schemas and play switch-a-roo. I only prefer this method because I used to do this trick in a job, and the warning message about renaming an object (which can't be suppressed) was filling up my history logs. Basically you need two additional schemas (one to hold a copy of the table temporarily, and one to hold the cached copy).

CREATE SCHEMA cache AUTHORIZATION dbo; CREATE SCHEMA hold AUTHORIZATION dbo;

Now, create a mimic of the table in the cache schema:

SELECT * INTO cache.table FROM dbo.table WHERE 1 = 0; -- then create any indexes etc.

Now when it comes time to refresh the data:

-- step 1: TRUNCATE TABLE cache.table; -- (if you need to maintain FKs you may need to delete) INSERT INTO cache.table SELECT ... -- step 2: -- this transaction will be almost instantaneous, -- since it is a metadata operation only: BEGIN TRANSACTION; ALTER SCHEMA hold TRANSFER dbo.table; ALTER SCHEMA dbo TRANSFER cache.table; ALTER SCHEMA cache TRANSFER hold.table; COMMIT TRANSACTION;

Theoretically, you could move the last transfer out of transaction, because users could start to query the new copy of dbo.table after the second transfer, but like I said, this is almost instantaneous so I'd be surprised if you see any difference in concurrency.

You could also optionally truncate cache.table again here, but I always kept it populated so I could compare data changes or troubleshoot if something went wrong. Depending on how long -- step 1 takes, it may be faster to perform the transfers in reverse than to re-populate from scratch.

Like rename, you can get wonky things from this process, such as statistics getting lost as they move with the actual table, they don't stick with the name. And like rename, you'll want to test this out and you may want to play around with isolation levels, e.g. RCSI for accessing the reporting table.

Answer2:

One solution would be to do it with that temp table you mentioned, and then just change it's name to production table name (but first, rename production table into something else). After that, you can just drop former production table. Of course, you should do all of that inside a transaction.

So, it would be:

-- Fill tmpTable -- -- Do renaming begin tran t1; execute sp_rename 'productionTable', 'productionTableBackup'; execute sp_rename 'tmpTable', 'productionTable'; commit tran t1;

Recommend

  • Validate textbox to accept only valid datetime value using DataAnnotations in mvc3
  • Attaching a fixed, transparent, header to a ListView?
  • Uber API: Specify multiple scopes while requesting or issuing a token returns Invalid Request Parame
  • SQL Service Broker: Collecting data from distributed sources.
  • TeamCity - setParameter value available to Build Feature
  • Convert date string value from U.S. to native Date with current locale U.S.?
  • iOS UIWebView: How can I figure out what the previous URL is?
  • MySQL select genres issue (php)
  • firebase unauth with google doesn't allow change of user
  • NullPointerException for lwuit.html.CSSEngine.applyStyleToUIElement(Unknown Source)
  • byebug, next into application code only
  • IIS 7.5 404 Error for .PDF files
  • How to show underscore (shortcut) without holding Alt?
  • System call time out?
  • HALF_PTR Windows data type
  • How can I print out a large table of symbols from MATLAB to form a cross-stitch chart?
  • Ruby and class variables in inherit class
  • Can XOR be expressed using SKI combinators?
  • Insertion large number of Entities into SQL Server 2012 [duplicate]
  • How to use tag-it
  • F#: In which memory area is the continuation stored: stack or heap?
  • ckeditor and jquery UI dialog not working
  • Add reference to ASP.NET 5 Class Library from Framework 4.5 Class Library Project
  • Data Access from Entity framework works during debugging but not on live
  • How do I get the list of bad records that didn't load in Bigquery?
  • Servlet stops working on Tomcat server after some hits or time
  • std::remove_copy_if_ valgrind bytes in block are possibly lost in loss record
  • How does document.ready work with angular element directives?
  • How solve “Qt: Untested Windows version 10.0 detected!”
  • C: Incompatible pointer type initializing
  • Functions in global context
  • Could not find rake using whenever rails
  • Highlight one bar in a series in highcharts?
  • How to delay loading a property with linq to sql external mapping?
  • Q promise. Difference between .when and .then
  • Lost migrations and Azure database is now out of sync
  • what is the difference between the asp.net mvc application and asp.net web application
  • Function pointer “assignment from incompatible pointer type” only when using vararg ellipsis
  • XCode can't find symbols for a specific iOS library/framework project
  • Run Powershell script from inside other Powershell script with dynamic redirection to file