I need the following, mutilple tables but only one active.
work_table_1 work_table_2 work_table_3 work_table_4
I need the following to work.
select * from work_table
work_table will point to the active 1-4 table. Is usng a view the best way to do this in SQL Anywhwre 12 or above? In oracle I use a synonym.
Is there only one user at a time who needs to access one of the 4 work tables under a certain name?
Then I would agree with Breck that views are handy for that. When you have to switch to another table, you simply have to adapt the view definition, i.e. use an ALTER VIEW statement, such as
create view work_table as select * from work_table_1; select * from work_table; -- will use wt 1 alter view work_table as select * from work_table_2; select * from work_table; -- will use wt 2 now
However, if several users (or connections or applications) need to be able to use one of these work tables, and they may have to use different ones, you could "hide" that detail behind a stored procedure that returns the desired table as result set. Note that with v9 and above, a stored procedure can be used inside the FROM clause of a select statement quite similar to a table/view.
You could then use a connection-specifig variable (cf. CREATE VARIABLE) or an input parameter to specify the desired work table. - If that fits your requirement, we might show a simple scetch as guideline...
answered 08 Jan '13, 15:45