CODEWITHSUNDEEP
sqlsql-servert-sql
DDoberman
DDoberman

Reputation: 1

SELECT INTO dynamically

I need to SELECT INTO destination dynamically, something like this:

if @param = 0
   :setvar TARGETTABLE  T_FOO
else
   :setvar TARGETTABLE  T_BAR

SELECT.... INTO $(TARGETTABLE) FROM......;

And looks like $(TARGETTABLE) always resolves to "T_BAR". How to do this properly ?

(SQLServer 2005)

Upvotes: 0

Views: 203

Answers (3)

Bill
Bill

Reputation: 4585

Since you are creating a new table, I assume that there isn't any pre-existing stuff that has dependencies on the table name. Why not create the table with some temporary name and then rename it?

SELECT.... INTO T_TEMP FROM......;

if @param = 0
   EXEC sp_rename 'T_TEMP', 'T_FOO'  
else
   EXEC sp_rename 'T_TEMP', 'T_BAR'

Upvotes: 2

user596075
user596075

Reputation:

SELECT INTO creates a table with the same schema as the table in the FROM clause. In other words, something like this:

select *
into NewTable
from ExistingTable

This would create NewTable with the same structure and schema as ExistingTable.

You could do:

if @param = 0
begin
    select *
    into NewTable1
    from ExistingTable
end
else
begin
    select *
    into NewTable2
    from ExistingTable
end

Or you could do this:

declare @newTableName varchar(100)
declare @dynamicSql varchar(1000)

if @param = 0
    set @newTableName = 'NewTable1'
else
    set @newTableName = 'NewTable2'

set @dynamicSql = 'select * into ' + @newTableName + ' from ExistingTable'

exec(@dynamicSql)

Depending on how many options (i.e. how many conditional statements you need), if this is two or three, I'd go with the former. If the new table name is a parameter to a stored proc, I'd go with dynamic SQL.

NOTE: in either way, the new table (the table name noted in ... INTO TableName has to be a nonexistent table for you to not throw an error.

Upvotes: 0

mwigdahl
mwigdahl

Reputation: 16578

You could either do this via dynamic SQL or by simply writing both queries. I'd suggest the latter:

IF (@param = 0)
    SELECT ... INTO T_FOO FROM ...;
ELSE
    SELECT ... INTO T_BAR FROM ...;

Upvotes: 0

Related Questions