create SQL Server table based on a user defined type

sql sql-server create-table
gt6989b picture gt6989b · Mar 13, 2014 · Viewed 36k times · Source

I have my defined table type created with

CREATE TYPE dbo.MyTableType AS TABLE
(
    Name      varchar(10) NOT NULL,
    ValueDate date        NOT NULL,
    TenorSize smallint    NOT NULL,
    TenorUnit char(1)     NOT NULL,
    Rate      float       NOT NULL
    PRIMARY KEY (Name, ValueDate, TenorSize, TenorUnit)
);

and I would like to create a table of this type. From this answer the suggestion was to try

CREATE TABLE dbo.MyNewTable AS dbo.MyTableType

which produced the following error message in my SQL Server Express 2012:

Incorrect syntax near the keyword 'OF'.

Is this not supported by SQL Server Express? If so, could I create it some other way, for example using DECLARE?

Answer

Dave Mason picture Dave Mason · Mar 13, 2014 
--Create table variable from type.
DECLARE @Table AS dbo.MyTableType

--Create new permanent/physical table by selecting into from the temp table.
SELECT *
INTO dbo.NewTable
FROM @Table
WHERE 1 = 2

--Verify table exists and review structure.
SELECT *
FROM dbo.NewTable

Related questions

Adding named foreign key constraints in a SQL Create statement

I currently have: CREATE TABLE galleries_gallery ( id INT NOT NULL PRIMARY KEY IDENTITY, title NVARCHAR(50) UNIQUE NOT NULL, description VARCHAR(256), templateID INT NOT NULL REFERENCES galleries_templates(id), jsAltImgID INT NOT NULL REFERENCES libraryImage(id) jsAltText NVARCHAR(500), dateCreated SMALLDATETIME …

Read More
SQL Server - Can you add field descriptions in CREATE TABLE?

I can see plenty of posts about where the field description extended property lives and how I can get it, but nothing about adding these at the CREATE TABLE stage. I'm dynamically creating tables so dynamically adding field descriptions would …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More