Database Programming

SQL Server: SELECT INTO inside both IF and ELSE blocks

I ran across this problem earlier today, and thought I should address this here in case anyone is interested in my solution.

When doing a SELECT INTO command against the same temp table in both IF and ELSE blocks, you will quickly learn that SQL Server complains that there “is already an object named ‘#temp’ in the database”. What one can do to avoid this problem is declare the temp table before the IF/ELSE block and insert into the table afterwards, but what if you want to use SELECT INTO instead?

if @var = 1
	select 'abc' into #temp;
	select 'def' into #temp;

select * from #temp;

This was my solution:

declare @temp_table varchar(25);
if @var = 1
	select 'abc' into #temp1;
	set @temp_table = '#temp1';
	select 'def' into #temp2;
	set @temp_table = '#temp2';

exec('select * from ' + @temp_table);

Fairly easy code, basically you’re creating one of the two possible temp tables to cover both blocks.

Initially, I figured I’d use the exec command (or more commonly known as EXECUTE) to create the temp table using SELECT INTO, but I found out that you cannot create the temp table inside the dynamic SQL as the rest of the procedure won’t be able to see it due to it being outside of scope. But it works vice versa where you can create the temp table inside the proc and then have access to it inside the dynamic SQL.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s