SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Back up a table with a dynamic filename
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nelsont
Starting Member

USA
25 Posts

Posted - 08/20/2013 :  10:54:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 08/20/2013 :  11:04:20  Show Profile  Reply with Quote
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);
Go to Top of Page

nelsont
Starting Member

USA
25 Posts

Posted - 08/20/2013 :  11:15:54  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 08/20/2013 :  12:42:43  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000