[SOLVED] Sql how to switch tables

Sql how to switch tables

I have two tables one of them have historical(cdr_hist) data other table have data from today(cdr_stage). My script must run every 30 minutes and calculate data from last 4 hours but every night at 12 all data move at cdr_hist.

The question is how I can switch and take data from history table when script run at 12:00 because cdr_stage is empty... I tried this:

IF  OBJECT_ID   ('[**CDR_Stage**]') IS NOT NULL 
BEGIN
    Select.....
    From **CDR_Stage**
END
ELSE
    Select.....
    From **CDR_Hist**
END

But its not work correctly... Any ideas??

Solution

You need to check the record existence instead of table existence

IF EXISTS (SELECT 1
           FROM   CDR_Stage)
  SELECT *
  FROM   CDR_Stage
ELSE
  SELECT *
  FROM   CDR_Hist

Or Dynamic Sql

DECLARE @sql VARCHAR(4000)

SET @sql = 'select * from '
           + CASE
               WHEN EXISTS (SELECT 1
                            FROM   CDR_Stage) THEN 'CDR_Stage'
               ELSE 'CDR_Hist'
             END

EXEC (@sql)