create SQL Server table based on a user defined type

Question

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 1

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

Answer 2

It is just like an other datetype in your sql server. Creating a Table of a user defined type there is no such thing in sql server. What you can do is Declare a variable of this type and populate it but you cant create a table of this type.

Something like this...

/* Declare a variable of this type */

DECLARE @My_Table_Var AS dbo.MyTableType;

/* Populate the table with data */

INSERT INTO @My_Table_Var 
SELECT Col1, Col2, Col3 ,.....
FROM Source_Table

Answer 3

Table type is a template. You need to use this object to create a table. The readonly is the only option you have.

Create proc NewT @x MyTableType readonly as Select * from @x

Now you can list the columns in the instantiated table calling the stored procedure. Exec NewT

Answer 4

Unfortunately this isn't possible in SQL Server or TSQL, unless something has changed in the latest versions.

This would be an amazingly useful feature if Microsoft would add it. (hint hint, Microsoft)

The best you can do is to create a table with the same structure as the user-defined table type, which is what the accepted answer does, but this isn't what you're asking for exactly, because the entire point of a user-defined table type is to be able to control the definition and change it whenever you want.

Answer 5

IN sql server use the following syntax to copy the table

SELECT * INTO newtablename FROM oldtablename;