Daily Table Refresh - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Daily Table Refresh

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-03-08 : 10:39:33

Hi All -

On a daily basis, I'm pulling (via SQL Agent) from a production table which contains records that were created in the past 365 days. This production table automatically purges anything older.

I need to keep track of older records, including those that have been purged in the production table. I need a cumulative table.

As such, I've set up a daily job which deletes anything in my own table IF a Request_number exists in the new table.

The PROBLEM is that sometimes the production_table is offline or a system outage will clear the production_table. Therefore clearing ALL records from my local table.

Is there a way I can say:
- IF production_table does not exist, or contain any records THEN Don't do anything. ELSE IF production_table does exist and contain records then RUN the code below ...

Below is my existing code:
THANKS!



DELETE FROM MY_TABLE T1
WHERE EXISTS (SELECT * FROM PRODUCTION_TABLE) T2 WHERE T1.REQUEST_NUMBER=T2.REQUEST_NUMBER)
GO

INSERT INTO MY_TABLE
SELECT * FROM PRODUCTION_TABLE 
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 10:42:10

just add


IF (SELECT COUNT(*) FROM productionTable)>0
BEGIN
...your code here
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-03-08 : 11:30:45

works like charm! THANKS !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 12:11:43

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources