SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Dynamic table switching possible?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aj84
Starting Member

1 Posts

Posted - 12/20/2012 :  10:41:17  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/20/2012 :  11:07:04  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 12/24/2012 :  07:46:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

USA
798 Posts

Posted - 12/24/2012 :  10:54:47  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000