How to create a temp table with the structure of an existing table given only the name(sql)

Question

I want to try to get the structure of a table into a temp table, given the name of the table in a local var @TableName, along with two new columns. I have seen links (such as this) that have a similar situation, but they do not try to maintain the same structure. I have tried many variations of code (I know the current iteration is not good) and have found myself stuck.

declare @TableName as nvarchar(40) = 'TableVar'
declare @Query as nvarchar(250) = 'select * into #ModifiedTable
FROM (
SELECT *,''I'' creation_status, GETDATE() creation_timestamp
FROM '+@TableName+') SubQuery';
exec sp_executesql @Query
DROP TABLE #ModifiedTable

You don't need to use a subquery here. Not clear what the real intention is here though. Sounds a LOT like an xy problem to me. http://xyproblem.info/ — Sean Lange, Jul 03 '18 at 18:12

score 0 · Answer 1 · answered Jul 03 '18 at 18:26

0

When you need to make a temp with the exact same schema, the easiest way is to do something like :

declare @TableName as nvarchar(40) = 'TableVar'
declare @Query as nvarchar(4000) 
SET @Query = 'select * 
into #ModifiedTable
FROM '+@TableName+' 
where 1=2';
print @query
exec sp_executesql @Query

The trick here is the 1=2. This guarantees no rows are returned, but the full schema is put into the temp table. Note I through a print statement in just so you can see the query to copy/paste debug.

answered Jul 03 '18 at 18:26

Holmes IV

1,673
2
23
47

2

Note that #ModifiedTable will be automatically dropped before sp_executesql returns, so you can't use it from the enclosing batch. – David Browne - Microsoft Jul 03 '18 at 18:41
@DavidBrowne-Microsoft thanks for mentioning that, I noticed it in testing, but assumed OP knew, but that might be the main problem :) – Holmes IV Jul 03 '18 at 18:44

How to create a temp table with the structure of an existing table given only the name(sql)

1 Answers1