How to dynamically Create TSQL table?

Question

I am using SSMS 2012 and wanting to do something like this:

If(Select count(*) from T1) > 0 
Begin
Select * into ##T3 from T2
end

If(Select count(*) from T1) < 0 
Begin
Select * into ##T3 from T4
end

The logic was created so that way I should technically only build T3 once, but I keep getting error saying ##T3 cannot be created because it already exists. Even if count(*) from T1 >0. It's like it's still creating the table from the first if statement.

I have also tried this:

If(Select count(*) from T1) > 0 
Begin
IF OBJECT_ID('tempdb..##T3') is not null Drop Table ##T3
Select * into ##T3 from T2
end

If(Select count(*) from T1) < 0 
Begin
IF OBJECT_ID('tempdb..##T3') is not null Drop Table ##T3
Select * into ##T3 from T4
end

put drop above your if will solve this problem – Tony Dong Mar 07 '18 at 22:35 — Tony Dong, Mar 07 '18 at 22:35
RUN THIS ONCE "drop table ##T" – DaniDev Mar 07 '18 at 22:37 — DaniDev, Mar 07 '18 at 22:37

monty · Accepted Answer · 2018-03-07T22:56:13.993

Note you can use an IF/ELSE and that you'd never get a COUNT less than 0

So, you can either do this dynamically:

IF(SELECT COUNT(*) FROM T1) > 0 
BEGIN
    EXEC('SELECT * INTO ##T3 FROM T2')
END
ELSE
BEGIN
    EXEC('SELECT * INTO ##T3 FROM T4')
END

Or, alternatively:

IF(SELECT COUNT(*) FROM T1) > 0 
BEGIN
    IF OBJECT_ID('tempdb..##T3') IS NOT NULL DROP TABLE ##T3
    SELECT * INTO ##T3 FROM T2
END
ELSE
BEGIN
    IF OBJECT_ID('tempdb..##T3') IS NOT NULL DROP TABLE ##T3
    SELECT * INTO ##T3 FROM T4
END

Note that trying to drop the table before the if/else statement is not sufficient. SSMS will still complain that ##T3 already exists in the SELECT INTO of the ELSE statement.

How to dynamically Create TSQL table?

1 Answers1