How to create a Table based on a View with the same column names and data types?

I found myself manually creating tables with identical column names and data types based on view-definitions…which felt a bit dump. As always, Google is my best friend. Found out that it is actually quite simple, by using the INTO clause. Of course I’ve used it before to store data in Temp-tables (#), but never thought about the possibility to use it for normal tables. 🙂

Let say you’ve created a view like this one:

CREATE VIEW dbo.vw_Resultset
AS
(
SELECT
CAST(1 AS INT) AS RecordId
,CAST(123 AS DECIMAL(9,2)) AS Amount
,CAST('NormalText' AS VARCHAR(20)) AS NormalText
,CAST(N'UnicodeText' AS NVARCHAR(50)) AS UnicodeText
,CAST(GETDATE() AS DATETIME) AS CurrentDateTime
,CAST(GETDATE() AS DATETIMEOFFSET) AS CurrentDateTimeOffset
)

How it looks in the physical world:

vw_Resultset

Ok, it’s time to create a new table with an identical structure (column names and data types):

SELECT *
INTO dbo.tbl_Resultset
FROM dbo.vw_Resultset

The result:

tbl_Resultset

Drop a comment if you haven’t thought about this option before, so I don’t feel alone! Feeling dump right now…I’ll now focus on a few, more advanced, problem solving / designing work 😀





Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s