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


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 '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
  • 1
    You created #tempTable and it didn't exist. This is exactly what SELECT INTO does. – Aaron Bertrand Apr 23 '13 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 '13 at 15:56
  • 1
    @daveboltman ..can you give an example where 1=2 is true ? – Kin Shah May 25 '18 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, privacy policy and cookie policy

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