torsdag den 15. marts 2012

Create SQL Table from View

Often I need to create a staging table to cache data from a view in, this can be quite timeconsuming because you have to find the datatype of each column in the view.
Today I found a way to do this very easily in MS SQL.

If you have a view "View1" and want to create a table "Table1" all you have to do is to execute the following script:

SELECT * INTO Table1 FROM View1

MS SQL identifies all the rows and generates a table, however it cannot see the datalengths, so if the view is selecting data from a table with a column of datatype nvarchar(255) but it only contains one value with the text "texttext" then the new table will get the datatype nvarchar(8).

All data from the view is also copyied.