jameslima (7/24/2016)
I was thinking that it might have data since this is going to be a regular thing. They want to run the job every month, so the table should have data in it from the previous flat file. At least I think so...maybe I am wrong? I am new at this.They want the table to only have the current data from the flat file but not the previous month or something.
You could test the staging table to see if it has data in it or not by using [font="Courier New"]IF EXISTS (SELECT * FROM dbo.Staging Table)[/font] but it doesn't actually matter if the staging table has data in it or not. An [font="Courier New"]INSERT INTO dbo.Archive Table FROM dbo.StagingTable [/font]will work just fine whether the staging table has anything in it or not and it will require one less "hit" in doing so. Once that's successful, then just TRUNCATE the staging table whether it had rows in it or not. It will also be successful. After that, load the staging table with data as you have already done.
--Jeff Moden
Change is inevitable... Change for the better is not.