INF: How to Create Dynamic Table Names in SQL Server

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"