Table with date..? 1

[Kujen]

Hi all,

I would like to create a new table every day with todays date after the tablename.
like: MyDatabase..MyTable_yymmdd

How can I do this?

Kent J.

 

[r937]

have you tried the CREATE TABLE statement?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

To create dynamic name you would need to use dynamic sql, e.g.

declare @sql nvarchar(8000), @d datetime
set @d = getdate()

set @sql = 'create table myFixedPart_' + CONVERT(VARCHAR,@d,112) + ' (myField1 ... '

print @sql

execute @sql

The question is - why do you need these tables created?

 

[Kujen]

I want a daily copy, that I need to save, of a specific table. Something like:
============
declare @sql nvarchar(8000), @d datetime
set @d = getdate()
set @sql='MyTable' + CONVERT(VARCHAR,@d,112)

select * into @sql
from MyTable
===========

Is this possible?

Kent J.

 

[markros]

No, you need to use dynamic SQL as I suggested already.

 

[simian336]

Markros... I think he may be saying that he wants to create the new table with the records from a source table rather then a new table from scratch.

Kujen needs to confirm that.

Simi

 

[markros]

It is not important. Even the insert into or select into command would have to be dynamic.

 

[Kujen]

OK!
So I the syntax should be:

declare @sql nvarchar(8000), @d datetimeset @d = getdate()
set @sql=
'select into MyTable' + CONVERT(VARCHAR,@d,112) + ' from MyTable'

print @sql
execute @sql

 

[markros]

Yes.