Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a #tempTable that was created using

INTO #tempTable
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;Database=MyFileName.xls', 
'SELECT * FROM [Sheet1$]')

Is there an easy way to copy the table structure of the #tempTable to a new physical table?

The temp table contains a lot of columns which contain numbers, dates, decimals, and strings, and I do not want to have to identify and manually define every column.

I am using SQL Server 2005

share|improve this question
up vote 8 down vote accepted

Why not use the same approach you used to create the #temp table?

SELECT * INTO dbo.NewTable FROM #tempTable;
share|improve this answer
... grumbles Beat me to it! – RoKa Apr 23 '13 at 15:42
Because NewTable doesn't exist... Will that work if the table doesn't exist? – Rachel Apr 23 '13 at 15:42
You created #tempTable and didn't exist. This is exactly what SELECT INTO does. – Aaron Bertrand Apr 23 '13 at 15:43
Yes, it will work. It will create the new (physical) table with the same structure – RoKa Apr 23 '13 at 15:44
Just to add a small more into to what @AaronBertrand already said that when you do a select * into Newtable from #temptable, it will copy the data as well. If you just want the table structure (not the data) then select * into Newtable from #temptable where 1=2 ... notice the where clause which is always false and hence will create a table but with no data. – Kin Apr 23 '13 at 15:56

Yes, just do :

select *
into dbo.NewTable
FROM #tempTable
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.