[SOLVED] create SQL Server table based on a user defined type

create SQL Server table based on a user defined type

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?

Solution

--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