SQL Server Agent - do not run if there is not data

I have a SQL Server Agent Job that runs and basically all it does is to truncate a table and then load the same table with new data. My issue is that If there in no new data to pull I  do not want to truncate the existing data. So the question is can I or how do I prevent the Truncate table part of the job if there is no data in the source table?
SeTechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ramprakash JeyabalanCommented:
Does your job execute a Stored Procedure? If yes, you can simply use EXISTS() clause to check the data existence in the source table in order to decide whether to truncate the destination table. Include this in your procedure
IF EXISTS(SELECT 1 FROM tblSource)
BEGIN
   TRUNCATE TABLE tblDestination
   INSERT INTO tblDestination SELECT * FROM tblSource
END

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Russell FoxDatabase DeveloperCommented:
You could try a COUNT on both tables, assuming the row count is the only relevant factor:
IF (SELECT COUNT(*) FROM TABLE1) <> (SELECT COUNT(*) FROM TABLE2)
BEGIN
    TRUNCATE...
END

Open in new window

If you need to check for field changes, you might need to track changes: when a row is updated, use a trigger or your update proc to update a DateChanged field, then only replace rows that have changed since the last replacement run. You could also look into creating a HASHBYTES field for each row and look for changed hashes, though I've found that to be too slow on large tables.
0
SeTechAuthor Commented:
Would work, but not for all my  circumstances
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.