Reputation: 4154
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
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
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