How do I append time stamp to a table name

Question

Post reply

How do I append time stamp to a table name

wlahue-773092

SSC Enthusiast

Points: 145
More actions
June 6, 2008 at 12:48 pm

#121885

How do I append time stamp to a table name?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You would need to use dynamic SQL to create the table. Like:

[font="Courier New"]DECLARE @sql VARCHAR(MAX)

SET @sql = 'Create Table test_table_' + CONVERT(VARCHAR(15), GETDATE(), 112) + '(test_id int primary key, test_name varchar(10))'

EXEC (@sql)

[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

wlahue-773092 SSC Enthusiast Points: 145 More actions · Answer 2

thank you for the script. It runs fine. My problem is I do not want to create a new table. I just want to make a copy of the table and rename it with attached time stamp. Any idea??

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Do you want the data as well? If you want to copy the data and the structure you just need to use select into in the dynamic sql. This will give you the columns structure and data. The code would be like this:

[font="Courier New"]DECLARE @sql VARCHAR(MAX)

SET @sql = 'Select * into table_name_' + CONVERT(VARCHAR(15), GETDATE(), 112) + ' From table_name'

EXEC (@sql)

[/font]

Oh and the Convert(varchar(15), getdate(), 112) gives you the iso format yyyymmdd, if you did not already know that.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Looks like Jack beat me, but here is my code anyways.

declare @SQLCmd nvarchar(4000),

@TableNameOrig sysname,

@TableNameCopy sysname

set @TableNameOrig = N'dbo.Student'

set @TableNameCopy = @TableNameOrig + N'_' + CONVERT(NVARCHAR(15), GETDATE(), 112)

set @SQLCmd = N'SELECT * INTO ' + @TableNameCopy + ' FROM ' + @TableNameOrig

print @SQLCmd

exec (@SQLCmd)

😎

wlahue-773092 SSC Enthusiast Points: 145 More actions · Answer 5

Thanks for the help from both of you. Your experiences are most appreciated by rookies like myself.