Create a table using a variable

• AlanM-242948

  Valued Member

  Points: 71

  More actions

  September 21, 2006 at 12:18 pm

  #115739

  I wish to create a table that is a snapshot of an inventory table at a chosen date, say tblInventoryItems_20060828. Using a param variable does not seem to work...e.g., @NewTblName.

  How can I create and table in a stored procedure and name it on the fly?

  Thanks,

  Alan Monteath

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  September 21, 2006 at 12:45 pm

  #661839

  use dynamic sql

  declare @param sysname, @sql nvarchar(4000)

  set @param ='tablename'

  set @sql='Create table '+ @param+'(sno int)'

  exec sp_executesql @sql

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  September 21, 2006 at 1:43 pm

  #661851

  Try this:

   

  declare @dt_Date Datetime,

   @s_SourceTableName varchar(255),

   @s_Sql nvarchar(4000),

   @s_ColName varchar(255),

   @i_Collength int,

   @s_ColType varchar(255),

   @i_Precision int,

   @i_Scale int

  set @dt_Date ='08/28/2006'

  set @s_SourceTableName ='MyTable'

  set @s_Sql =N'If exists (Select 1 from sysobjects where name =''' + @s_SourceTableName + '_' + Convert(Char(8),@dt_Date,112) + ''') drop table ' + @s_SourceTableName + '_' + Convert(Char(8),@dt_Date,112) + ''

  exec(@s_Sql)

   

  set @s_Sql =N'Create table ' + @s_SourceTableName + '_' + Convert(Char(8),@dt_Date,112) + '( '

  Declare Col_cursor cursor for

  select COL.Name,COL.length,STYP.Name,COL.xprec,COL.xscale

    from sysobjects OBJ INNER JOIN syscolumns COL on OBJ.id =COL.id

   INNER JOIN systypes STYP ON COL.xtype=STYP.xtype

  where OBJ.name =@s_SourceTableName

  Open Col_cursor

  Fetch next from Col_cursor into @s_ColName, @i_Collength, @s_ColType,@i_Precision,@i_Scale

  While @@FETCH_STATUS = 0

  Begin

   If @s_ColType ='decimal'

   Begin

    set @s_Sql = @s_Sql + '[' + @s_ColName + '] [' + @s_ColType + '] (' + Cast(@i_Precision as varchar(255)) + ',' + Cast(@i_Scale as varchar(255)) +  '), '

   End

   Else

   Begin

    If @s_ColType ='char' or @s_ColType ='nchar' or @s_ColType ='varchar' or @s_ColType ='nvarchar'

    Begin

     set @s_Sql = @s_Sql + '[' + @s_ColName + '] [' + @s_ColType + '] (' + Cast(@i_Collength as varchar(255)) + '), '

    End

    Else

    Begin

     set @s_Sql = @s_Sql + '[' + @s_ColName + '] [' + @s_ColType + '] ' + ', '

    End

   End

   select @s_Sql

   Fetch next from Col_cursor into @s_ColName, @i_Collength, @s_ColType,@i_Precision,@i_Scale

  End

  Close Col_cursor

  Deallocate Col_cursor

  select @s_Sql = left(ltrim(rtrim(@s_Sql)),len(ltrim(rtrim(@s_Sql)))-1) + ')'

  select @s_Sql

  exec(@s_Sql)

  select @s_Sql = 'Insert into ' + @s_SourceTableName + '_' + Convert(Char(8),@dt_Date,112)  + ' Select * from ' + @s_SourceTableName

  select @s_Sql

  exec (@s_Sql)

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  September 21, 2006 at 1:46 pm

  #661852

  Or this one:

  declare @dt_Date Datetime,

   @s_SourceTableName varchar(255),

   @s_Sql nvarchar(4000)

   

  set @dt_Date ='08/28/2006'

  set @s_SourceTableName ='MyTable'

  set @s_Sql =N'If exists (Select 1 from sysobjects where name =''' + @s_SourceTableName + '_' + Convert(Char(8),@dt_Date,112) + ''') drop table ' + @s_SourceTableName + '_' + Convert(Char(8),@dt_Date,112) + ''

  exec(@s_Sql)

  set @s_Sql =N'Select * into ' + @s_SourceTableName + '_' + Convert(Char(8),@dt_Date,112) + ' from ' + @s_SourceTableName

  select @s_Sql

  exec(@s_Sql)