Sql-server – copy the table structure of a #temp table to a new physical table

My original answer was working (changing int to nvarchar) but I ran into another column which contained dates in the excel source file where some cells contained the string "NULL" (I did not want to have dates in a NVARCHAR column.) When SSIS got to this date column it was generating an error because it could not convert the string "NULL" to a date. It was not properly reading the "NULL" as a NULL value, instead it was reading it as a string. The way I was able to resolve this issue is by adding a derived column component to the package that replaced the date field by using the following expression

(DT_WSTR,255)Auth_from_date == "NULL" ? NULL(DT_WSTR,50) : (DT_WSTR,255)Auth_from_date

this is just an IF statement that will change the string "NULL" to an actual NULL value. The date is then passed thru a data conversion component and converted into DATETIME format.

If the table has an index, you may want to consider the cluster command instead of re-creating it or using vacuum full. This will:

• Have the same effect on dead tuples - it physically re-writes the whole table
• Retains any existing indexes
• Might improve performance more than just removing dead tuples, depending on whether you will benefit from the clustering too (though of course you could achieve the same with an order by clause when re-creating)