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.

  • RevoScaleR: rxPredict, the number of parameters does not match the number of variables
  • The multi-part identifier could not be bound on SQL Server 2008
  • How to create temp tables in SQL Server via MS Access
  • Stored procedure not accepting any value
  • Parameter Sniffing (or Spoofing) in SQL Server
  • any help would be great!

  • Possible to get SqlTransaction from ambient Transaction?
  • SqlBulkCopy and DataTables with Parent/Child Relation on Identity Column
  • Localdb connection from c# .net 3.5
  • Prompt for Database Connection String
  • Using Enum for a data layer object's 'status' in C#
  • How do I specify the database isolation level to be used, at a high level?
  • 2 Solutions collect form web for “Most efficient way to move table rows from one table to another”

    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).


    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: 
      ALTER SCHEMA hold  TRANSFER dbo.table;
      ALTER SCHEMA dbo   TRANSFER cache.table;
      ALTER SCHEMA cache TRANSFER hold.table;

    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.

    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;
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.