Microsoft KB Archive/175850

= INF: How to Create Dynamic Table Names in SQL Server =

Article ID: 175850

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q175850



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: kbhowto kbcode kbusage KB175850

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.