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.
| 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 beEXEC ('Select column1, column2, column3 FROM ' +@tblname + ' WHERE x ....')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-12-24 : 07:46:25
|
| Make sure to read this fully www.sommarksog.se/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
|
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); |
|
|
|
|
|
|
|
|
Dynamic table switching possible?