table names

Question

table names

Roger-461008

Newbie

Points: 3
More actions
September 24, 2008 at 12:28 am

#193159

Please can someone help.
I am trying to write SQL that will create a table name with a date stamp (mmyy) derived from getdate().
Any suggestions?
Ta

Viewing 9 posts - 1 through 8 (of 8 total)

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

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

You could use dynamic SQL to build the Create Table statement, including the DatePart function to extract the mmyy of GetDate().

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

Roger (9/24/2008)
Please can someone help.
I am trying to write SQL that will create a table name with a date stamp (mmyy) derived from getdate().
Any suggestions?
Ta

Are you trying to create tables every month?

declare @sql varchar(1000)

set @sql='create table test_'+right('0'+cast(month(getdate()) as varchar(2)),2)+right(year(getdate()),2)

exec(@sql)

Madhivanan

Failing to plan is Planning to fail

roger-889173 Grasshopper Points: 19 More actions · Answer 3

Madhivanan

Many thanks for the quick response.

When I run your code I get an error message "incorrect syntax near test_0908"

I will need to run this code each month as was hoping to adapt it 'select * into' as follows:

declare @sql varchar(1000)

set @sql=

'select * into dbo.test_'+right('0'+cast(month(getdate()) as varchar(2)),2)+right(year(getdate()),2) from mra_arch_tbl

exec(@sql)

this generates an error message "incorrect syntax near the keyword from' .

Can you help?

John H Marsh SSChampion Points: 11671 More actions · Answer 4

Hello.

With dynamic SQL you have to build a valid Create table or Select statement in a variable (in this example @sql).

The easiest way to see if it is valid is to use Print @sql and then copy the statement generated into another query window and execute it.

You need to be careful with the quotes when building dynamic SQL, and this is what is wrong with your select. (Please see below)

Regards,

John Marsh

declare @sql varchar(1000)

set @sql=

'select * into dbo.test_'+right('0'+cast(month(getdate()) as varchar(2)),2)+right(year(getdate()),2) + ' from mra_arch_tbl'

Print @sql

-- exec(@sql)

www.sql.lu
SQL Server Luxembourg User Group

roger-889173 Grasshopper Points: 19 More actions · Answer 5

John

Thanks for the advice.

The dynamic sql seems to be working OK as it is generating 0908 the problem I have is how to incorporate the dynamic sql into my query.

I thought my sql code would need speech marks around it like:

declare @sql varchar(1000)

set @sql=

'select * into dbo.test_'+right('0'+cast(month(getdate()) as varchar(2)),2)+right(year(getdate()),2) 'from mra_arch_tbl'

exec(@sql)

but this is obviously wrong

Then I tried

declare @sql varchar(1000)

set @sql=right('0'+cast(month(getdate()) as varchar(2)),2)+right(year(getdate()),2)

select * into dbo.test_+exec(@sql) from mra_arch_tbl

but again it doesn't work.

Please can you advise.

Ta

John H Marsh SSChampion Points: 11671 More actions · Answer 6

Hello Again,

The first version is more valid as you need to create a complete SQL statement. You can not create partially dynamic SQL as in your second version.

Below is a corrected version.

Regards,

John Marsh

declare @sql varchar(1000)

set @sql=

'select * into dbo.test_'+right('0'+cast(month(getdate()) as varchar(2)),2)+right(year(getdate()),2) + ' from mra_arch_tbl'

Print @sql -- Display for debug puposes

exec(@sql)

www.sql.lu
SQL Server Luxembourg User Group

roger-889173 Grasshopper Points: 19 More actions · Answer 7

roger-889173

Grasshopper

Points: 19

September 24, 2008 at 2:41 am

#876177

John

Excellent code many thanks for your help.

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 8

I'm not sure who gave you the requirements on these file names, but the yyyymm format will serve you much better and for much longer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)