Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Dynamic table switching possible?

Author  Topic 

aj84
Starting Member

1 Post

Posted - 2012-12-20 : 10:41:17
Hi all,

New on here though I have been browsing a fair bit :- )
I have a query on a ... query using dynamic table switching.

Long story short, there are two table sources both identical in structure but called for the sake of this thread - T_old_A and T_new_A.

My query is a simple "select columns from t_old_A where x" for the daytime however on an overnight load, it has to be "select columns from t_new_A where x"

This would be the case for around 30 different tables (so there are 60 t_old and t_new style tables).

The decision on what table is used is based on a table query with an active flag on it. If "new" = 1 then the scripts use t_new_A/B/C etc and if old... you get the point.

This works currently using
Declare @tblname VARCHAR(50)
SET @TblName = (SELECT Table_Name From [dbo].[Tables] WHERE Module = 'A' AND Build_Type_ID =
(SELECT Build_Type_ID FROM [dbo].[Build_Type_Switch] WHERE Active = 1)
)

EXEC ('Select column1, column2, column3 FROM ' +@tblname)

However for this to work in a SP and finally in a SSIS package, I have to use SET NoCOUNT on and SET FMTONLY OFF not to mention I still have to add the where clause in the above script which I am not succeeding in!

So, firstly, is there an easier way?
I am happy to use the method above for all the scripts that need it but how do I use a where clause in the above exec style query?

EXEC ('Select column1, column2, column3 FROM ' +@tblname WHERE x) does not want to work (Incorrect syntax near the keyword 'WHERE'.)

Hopefully I am overcomplicating things and there is a simpler solution!

Cheers,
AJ

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-20 : 11:07:04
it should be

EXEC ('Select column1, column2, column3 FROM ' +@tblname + ' WHERE x ....')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-12-24 : 07:46:25
Make sure to read this fully www.sommarksog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-12-24 : 10:54:47
I would recommend that you look at synonyms to solve this issue.

In your code, you would reference the synonym for the table - and in a separate process switch the synonym from old to new as soon as the new table has been built and is ready.

You can use dynamic SQL to determine which table is active and reset the synonyms that way. For example:

Declare @tblname VARCHAR(50)
SET @TblName = (SELECT Table_Name From [dbo].[Tables] WHERE Module = 'A' AND Build_Type_ID =
(SELECT Build_Type_ID FROM [dbo].[Build_Type_Switch] WHERE Active = 1)
)

Execute('Drop Synonym dbo.CurrentTableA; Create Synonym dbo.CurrentTableA On ' + @tblName);
Go to Top of Page
   

- Advertisement -