copy table structure only for new table

[influent]

I need to create a new table every week, and name it whatever the date is, e.g. tbl082802. The original table has six columns, and I don't want to type in every column's name and length when I create the new table every week. Is there a quick way to create a new table that copies the structure of an existing table?

 

[philhege]

declare @L_dtoday datetime
declare @l_ctablename char(16)
declare @l_csqlstring char(128)
set @l_dtoday = getdate()
set @l_ctablename = 'tbl'+replace(cast(@l_dtoday as char(12)),'/','')
set @l_ctablename = replace(@l_ctablename,' ','')
print @l_ctablename
set @l_csqlstring = 'select top 100 * into '+@l_ctablename+' from mytemplatetable'
print @l_csqlstring
exec(@l_csqlstring)

HTH,
Phil Hegedusich
Senior Web Developer
IIMAK

 

[philhege]

Er, change top 100 to top 1... and

set @l_cSQLstring = 'DELETE FROM '+@l_ctablename
exec @l_cSQLSTring

Sorry for the omission.

PH

 

[tlbroadbent]

Try this.

Declare @sql varchar(200)

Select @sql=
'Select * Into Tbl'+
replace(convert(varchar(8),getdate(),1),'/','')+
' From YourBaseTable Where 1=2'

--Optional SQL statement print
--Print @sql

Exec(@sql) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.