SQLTeam.com | Weblogs | Forums

How to flip tables in sql server 2014


#1

Hello,

I have a requirement wherein there are 2 tables (Staging & Target) in the same database.
Everytime data is first loaded in the Staging table. Now in second run data will be again first Loaded to the Staging table. Now I want to flip the tables using SQL query in such a way that after data is loaded into the Staging table make this changes

Staging becomes(flip) Target
Target becomes(flip) Staging

So ideally we will see both tables. But in actual at a time only 1 table has latest data.
Before opting for flip tables approach I have tried the sp_rename but that results in deadlock if someone tried to query Target table while it is being dropped and getting renamed.

Example,

IF OBJECT_ID('[dbo].[Target]','U') IS NOT NULL DROP TABLE [dbo].[Target] ;
EXEC sp_rename '[dbo].[Staging]','Target';

If we use the flip approach then there will be minimal chances of a lock. I tried to understand this flip tables concept and one approach I see is, it could be done using some kind of flag setting in SQL but not sure how. Any help on this would be really appreciated.


#2

what is this "flip" approach that you are talking about ? I don't think such feature exists in SQL Server.


#3

If you can explain the underlying problem you are trying to solve, there may be simpler solutions than "flipping" the tables.

In general, it is a very bad idea to make schema changes - such as the sp_rename that you are attempting - as part of your regular DML queries. So you must find alternate ways of accomplishing the goal.

One option would be to have conditional branching in your queries to tell which table to look into based on timestamps in the tables, or some other indicator.


#4

Basically what I want to do is there will be 2 tables. data will be first always loaded to Staging table, now I want the data of Staging to be in Target table.

Reason Staging table will every time have new columns/ data which won't match with Target table. And Target table is what everyone will use for querying (SELECT) .

So while process of loading data from Staging to Target is in progress there should not be any lock for users who are querying on Target table because currently I have to drop Target table and rename staging to Target.

This is what I want to achieve data of staging should be in Target without dropping Target.
Flip might not be the right term, may be its called Partition Switching, but any approach in sql that could help me achieve my goal of not dropping target table and getting latest data of Staging will be helpful.


#5

Take a look at using ALTER SCHEMA....TRANSFER. Here are a few articles:

https://sqlperformance.com/2012/08/t-sql-queries/t-sql-tuesday-schema-switch-a-roo
https://sqlperformance.com/2013/04/t-sql-queries/schema-switch-a-roo-part-2

Michael's article is part of a series on modifying tables, the one I posted specifically mentions schema transfer. You should also read the rest of the series, the links are near the top.

Aaron's article mention difficulty with triggers. I've used the schema transfer technique successfully with triggers on affected tables. The point to keep in mind is to NOT use 2-part names (schema.table) in the trigger code for any table that is going to switch schemas. The other issues he mentions with foreign keys are definitely problematic.

The technique that I used depended on 3 schemas (dbo, Swap, and Load). The Load tables were always truncated and inserted fresh data. When completed, all dbo tables would transfer to swap:

ALTER SCHEMA Swap TRANSFER dbo.table1;

Then all Load tables would transfer to dbo:

ALTER SCHEMA dbo TRANSFER Load.table1;

And finally move Swap over to Load:

ALTER SCHEMA Load TRANSFER Swap.table1;

This retains the original data, and can be swapped back if necessary using the exact same process. All of the tables to be swapped should be done in a single transaction, as you need to hold schema locks on the tables so that other modifications are blocked until the switch is complete. This is the same kind of metadata operation as renaming, but with none of the naming hassles. It's very fast, we've swapped out 25+ tables in 100-200ms typically.


#6

I would use Synonyms for this.


#7

Can you please give an example, On how Synonyms can be used in this scenario?


#8

Point the synonym to the table not being loaded. When you want to load that table point it to the other table.

CREATE SYNONYM dbo.Mytable FOR dbo.table1;
-- truncate and load table2
DROP SYNONYM dbo.Mytable;
-- truncate and load table1
CREATE SYNONYM dbo.Mytable FOR dbo.table2;