SQL: How to Create Dynamic Table Names in SQL Server

http://support.microsoft.com/kb/175850

INF: How to Create Dynamic Table Names 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 table @mytable (col1 int)

-- 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)