INF: How to Create Dynamic Table Names in SQL Server

ID: Q175850

The information in this article applies to:

Microsoft SQL Server versions 6.0, 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)

Notice that you can enhance this further if you need more than milliseconds precision (that is, if you need to create more than one table in the same millisecond) by adding username, spid(@@spid) or other data as part of the table name.

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"

Additional query words: names tablename

Keywords : kbcode kbusage SSrvGen
Version : WINNT:6.0 6.5
Platform : winnt
Issue type : kbhowto