Sql how to switch tables

2.4k views Asked by At

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??

2

There are 2 answers

0
Pரதீப் On BEST ANSWER

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) 
0
sagi On

No need for IFs , that can be done with pure sql using UNION and NOT EXISTS() :

SELECT * FROM CDR_Stage
UNION ALL
SELECT * FROM CDR_Hist
WHERE NOT EXISTS(SELECT 1 FROM CDR_Stage) -- Second select will return data only if first one won't .