How to create a temporary table with a different name

xdy0818

How to create a different table name each time in the TEMPDB database in SQL Server.

For example, the first time I run the program is to create a temporary table tempdb..tmp01
When the program is called for the second time, it will create tempdb..tmp02 so that the table name will not be duplicated.

wgfxman

Use dynamic EXEC statement

小老鹰

Basic syntax of dynamic sql statement
1: Normal SQL statements can be executed with Exec

eg: Select * from tableName
         Exec ('select * from tableName')
         Exec sp_executesql N'select * from tableName '-please note that N must be added before the string

2: When the field name, table name, database name and the like are used as variables, dynamic SQL must be used

eg:
declare @fname varchar (20)
set @fname = 'FiledName'
Select @fname from tableName-Error, no error will be prompted, but the result is a fixed value FiledName, which is not what you want.
Exec ('select' + @fname + 'from tableName')-Please note that there are spaces around the single quotes before and after the plus sign

Of course, you can change the string to a variable
declare @fname varchar (20)
set @fname = 'FiledName'-set the field name

declare @s varchar (1000)
set @s = 'select' + @fname + 'from tableName'
Exec (@s)-success
exec sp_executesql @s-This sentence will report an error



declare @s Nvarchar (1000)-Note that this is changed to nvarchar (1000)
set @s = 'select' + @fname + 'from tableName'
Exec (@s)-success
exec sp_executesql @s-this sentence is correct

3. Output parameters
declare @num int,
        @sqls nvarchar (4000)
set @ sqls = 'select count (*) from tableName'
exec (@sqls)
--How to put the execution result of exec into the variable?

declare @num int,
               @sqls nvarchar (4000)
set @ sqls = 'select @ a = count (*) from tableName'
exec sp_executesql @ sqls, N '@ a int output', @ num output
select @num

wgfxman

declare @tablename varchar (200)
select @ tablename = left ('TMP00', 5-len (isnull ((select cast (right (MAX (name), 2) as int) +1 from sysobjects where name like 'tmp%' and type = 'u') ,'1')))
+ cast (isnull ((select cast (right (MAX (name), 2) as int) +1 from sysobjects where name like 'TMP%' and type = 'U'), 1) as char (2))

exec ('create table' + @ tablename + '(testid int)')

xdy0818

Up, the problem is solved.

xdy0818

declare @tablename varchar(200)
select @tablename=left('TMP0000',7-len(isnull((select cast(right(MAX(name),4) as int)+1 from sysobjects
where name like'tmp%' and type='u'),'1')))+cast(isnull((select cast(right(MAX(name),4) as int)+1 from sysobjects where name like ' tmp%' and type='U'),1) as char(4))
--print @tablename
exec('create table'+@tablename+'(testid int )')