INF: How to Create Dynamic Table Names in SQL Server

• Microsoft SQL Server versions 6.0 and 6.5

SUMMARY

If you need a temporary table to persist across non-nested stored procedures and then you want to drop the temporary table when you no longer need it, you can dynamically create permanent tables in your code. This article provides an example of this procedure.

MORE INFORMATION

You can use the following code to create a dynamic table name in SQL Server:

   -- Variable that will contain the name of the table
   declare @mytable varchar(30)
   -- Creates a temp table name
   select @mytable ='TEMP'+ CONVERT(char(12), GETDATE(), 14)
   print @mytable

   -- Table cannot be created with the character  ":"  in it
   -- The following while loop strips off the colon
   declare  @pos int
   select   @pos = charindex(':',@mytable)

   while @pos > 0
      begin
           select @mytable = substring(@mytable,1,@pos - 1)   +
                             substring(@mytable,@pos + 1,30-@pos )

           select @pos = charindex(':',@mytable)
      end
   print 'Name without colon is :'
   print @mytable

   -- Create the temporary table
   execute ("create table "+ @mytable +
             "(col1 int)" )


   -- Insert two rows in the table
   execute ("insert into " + @mytable +
                 " values(1)")
   execute ("insert into " + @mytable +
                 " values(2)")

   -- Select from the temporary table
   execute ("select col1 from " + @mytable )
   -- Drop the temporary table
   execute ("drop table " + @mytable)

You can use part of the code above to strip unwanted characters from a string. For example, assuming you have the string "De La Hoya", you can use the WHILE loop above to get a resulting string of "DeLaHoya"