Back up a table with a dynamic filename

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

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Back up a table with a dynamic filename

Author

Topic

nelsont
Starting Member

25 Posts

Posted - 2013-08-20 : 10:54:46

I want to run this:

select *
into Backup_table
from Main_table

Then I want to truncate Main_table.

The problem is that I want to run this every so often automatically and I don't want to overwrite Backup_table (or get the error that it already exists).

How can I generate a file name for Backup_table dynamically that includes a date at the end of it? For example, Backup_table_08202013.

I can't figure out how to create the dynamic SQL statement that I can exec(). I can easily write one that I can print, then cut / paste from the result window and run it, but since I want to put this in an ETL that is no good. Any help is appreciated.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-20 : 11:04:20

You can use dynamic SQL - for example like shown below if you want to append date/time to the filename. Backing up into a new table each time you want to take a backup is generally not a good idea for various reasons. There are other alternatives - everything from taking the backup of the whole database, backing up into a single table with an additional column to indicate the backup set, or saving a delta of the changes etc.

DECLARE @sql NVARCHAR(4000);
SET @sql = 'select * into Backup' 
	+ REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(32),GETDATE(),120),'-',''),' ',''),':','') 
	+ ' from MainTable'
EXEC (@sql);

nelsont
Starting Member

25 Posts

Posted - 2013-08-20 : 11:15:54

Yes I know this is not ideal but it is an odd situation. The code you wrote works great. I think I was reading too much into it when I was trying to write it. Thanks very much.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-20 : 12:42:43

You are very welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources