Create a Table Using the current Date as the Name 1

[elmo29]

I am processing data each day and the data processed needs to be stored for possible use later.&nbsp;&nbsp;<br><br>The incoming data is in a text file and I use a DTS schedule to import and then run a stored procedure.&nbsp;&nbsp;<br><br>What I would like to know is if it is possible to create a table for my data based on the date it was processed e.g yyyymmdd.&nbsp;&nbsp;I would like to create the table after I have processed my data - i.e. because I am moving it to a different server (which we use as an archive - for customer queries)<br><br>How would I go about doing something like this? And what would be the best way?<br><br>Any help would be gratefully received

 

[clawton]

You cannot use a variable as the tablename within a create table statement.<br><br>However the EXEC statement can execute a piece of SQL stored within a variable.&nbsp;&nbsp;What you can do is create a stored procedure that stores your entire CREATE TABLE statement into a variable, substituting the tablename with the current date.&nbsp;&nbsp;The code within this variable is then executed, creating the table.&nbsp;&nbsp;The following code is an example :<br><br>------------------------------------<br>IF Exists (Select Name<br> FROM SysObjects<br> WHERE Name = 'CreateTable' AND Type = 'P' )<br> DROP Procedure CreateTable<br>GO<br><br>CREATE PROCEDURE CreateTable<br>AS<br>&nbsp;&nbsp;DECLARE @CString VARCHAR(1000)<br>&nbsp;&nbsp;DECLARE @CDate&nbsp;&nbsp;&nbsp;VARCHAR(10)<br><br>&nbsp;&nbsp;SET @CDate = LEFT(CONVERT(VARCHAR,GetDate(),102),4)+<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUBSTRING(CONVERT(VARCHAR,GetDate(),102),6,2)+<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RIGHT(CONVERT(VARCHAR,GetDate(),102),2)<br><br> SET @CString = 'CREATE TABLE MyTable'+@CDate<br> SET @CString = @CString + ' ( Column1 VARCHAR(10),'<br> SET @CString = @CString + ' Column2 VARCHAR(20),'<br> SET @CString = @CString + ' Column3 DECIMAL )'<br><br> EXEC(@CString)<br>GO<br>----------------------------------<br>By running the CreateTable stored procedure, a table is created named MyTableyyyymmdd with three columns.<br><br>This procedure can be run either from within other stored procedures, or directly from DTS.<br><br>Note that a table name cannot begin with a number, so a tablename of just the date is invalid - it needs to start with at least one alphabetic character.<br><br>Hope it helps<br><br>Chris.<br>