CODEWITHSUNDEEP
sql-serversql-server-2012
APH
APH

Reputation: 4154

Create table on the fly using select into

I am trying to use dynamic SQL to fill a temp table with data from one of several servers, depending on a declared variable. The source data may have more columns added in the future, so I'd like to be able to create the destination temp table based on what columns currently exist, without having to explicitly define it.

I tried creating an empty table with the appropriate columns using:

Select top 1 * into #tempTable from MyTable
Delete from #tempTable

Or:

Select * into #tempTable from MyTable where 1 = 0

Both worked to create an empty table, but when I then try to insert into it:

declare @sql varchar(max) = 'Select * from ' 
+ case when @server = '1' then 'Server1.' else 'Server2.' end
+ 'database.dbo.MyTable'

Insert into #tempTable
    exec(@sql)

I get this error:

Msg 213, Level 16, State 7, Line 1 Column name or number of supplied values does not match table definition.

exec(@sql) works fine on its own. I get this error even when I use the same table, on the same server, for both steps. Is this possible to fix, or do I have to go back to explicitly defining the table with create table?

Upvotes: 1

Views: 9455

Answers (2)

Myo Myint Aung
Myo Myint Aung

Reputation: 147

How about using global temp table. there is some disadvantage of using global temp table because it can access from multiple users and databases. ref http://sqlmag.com/t-sql/temporary-tables-local-vs-global

DECLARE @sql nvarchar(max) = 'SELECT * INTO ##tempTable FROM ' 
+ case when @server = '1' THEN 'Server1.' ELSE 'Server2.' END
+ 'database.dbo.MyTable'

EXECUTE sp_executesql (@sql)

SELECT * FROM ##tempTable

Upvotes: 1

APH
APH

Reputation: 4154

(Thanks to helpful commenter @XQbert)

Replacing the ID column (Int, Identity) in the temp table with a column that was just an int causes

Insert into #tempTable
    exec(@sql)

to function as intended.

Both that syntax and 

declare @sql varchar(max) = 'Insert into #tempTable Select * from ' 
+ case when @server = '1' then 'Server1.' else 'Server2.' end
+ 'database.dbo.MyTable'

exec(@sql)

worked, but making insert part of the dynamic sql produced much more helpful error messages for troubleshooting.

Upvotes: 1

Related Questions