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

2 Answers 2


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

SELECT * INTO dbo.NewTable FROM #tempTable;
  • ... grumbles Beat me to it!
    – RoKa
    Apr 23, 2013 at 15:42
  • Because NewTable doesn't exist... Will that work if the table doesn't exist?
    – Rachel
    Apr 23, 2013 at 15:42
  • 1
    You created #tempTable and it didn't exist. This is exactly what SELECT INTO does. Apr 23, 2013 at 15:43
  • 4
    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 Shah
    Apr 23, 2013 at 15:56
  • 1
    @daveboltman ..can you give an example where 1=2 is true ?
    – Kin Shah
    May 25, 2018 at 11:58

Yes, just do :

select *
into dbo.NewTable
FROM #tempTable

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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