CODEWITHSUNDEEP
sqlsql-server
Sylar
Sylar

Reputation: 12082

Create a temp table with pre-filled columns

Im trying to create a temp table #TempTable with columns. There are many columns and I do not want to type them all out by hand. Is there a way to pre-fill, if that makes sense?

Instead of 

CREATE #TempTable (col1, col2 ... col1000) -- Im not saying we have 1000

But doing:

CREATE #TempTable (SELECT column_name 
                   from information_schema.columns where table_name = 'OriginalTable')

Is this possible? Im using MS SQL.

Upvotes: 1

Views: 325

Answers (3)

Abhishek
Abhishek

Reputation: 2490

One way of doing -

select top 0 * into #TempTable from OriginalTable

The above creates an empty copy of temp table

If you don't want to specify *(wildcard) and want specific columns from OriginalTable to be created in your temporary table -

select top 0 col1,col2,col3 into #TempTable from OriginalTable

Upvotes: 1

martijn
martijn

Reputation: 1469

When creating a temp table, it's good to clean up before creating it. repeating this step will cause an error if the table already exists

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp

select * 
into #tmp 
from OriginalTable

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do SELECT . . . INTO :

SELECT ot.* INTO #TempTable 
FROM OriginalTable ot
WHERE 1 = 0;

Note : When using the SELECT . . . INTO statement, the #TempTable must not already exist.

Upvotes: 3

Related Questions