CODEWITHSUNDEEP
sqlsql-server
user11740857
user11740857

Reputation: 480

create dynamic temp table in sql

Is there a way to create a dynamic temp table. Below sql code is declaring a variable @tic. I am planning to insert contents from table1 to temp table #df. So instead of giving directly as #df, I am passing as a variable. But below is code is not successful. Can anyone help me here?

declare @tic as varchar(100) = 'df'

select *
into '#' + @tic from (
select * from [dbo].[table1])

select * from #df

Upvotes: 0

Views: 175

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Is there a way? Well, I think of the answer as "yes and no and maybe".

As far as I know, there is no way to do this using a local temporary table. As Stu explains in the comment, you would need dynamic SQL to define the table name and then the table would not be visible in the outer scope, because it is a local temporary table.

The "yes" is because one type of temporary table are global temporary tables. These are tables that persist across different scopes. And they are defined using ## instead of # as the prefix. So this works:

declare @tic as varchar(100) = 'df'

declare @sql nvarchar(max);
set @sql = 'select * into ##' + @tic + ' from table1';

select @sql;

exec sp_executesql @sql;

select * from ##df;

(Here is a db<>fiddle.)

The "maybe" is because I'm quite skeptical that you really need this. Dynamic table names are rarely useful in SQL systems, precisely because they depend on dynamic SQL. Introducing dynamic names into SQL (whether columns or tables) is dangerous, both because of the danger of SQL injection and also because it can introduce hard-to-debug syntax errors.

If you are trying to solve a real problem, there might be alternative approaches that are better suited to SQL Server.

Upvotes: 1

Related Questions