Create a SQL Server table based on a user-defined type

Question

I have a specific type of table created using

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 it was suggested to try

 CREATE TABLE dbo.MyNewTable AS dbo.MyTableType 

causing the following error message to appear in my SQL Server Express 2012:

Incorrect syntax next to the keyword 'OF'.

Is this not supported by SQL Server Express? If so, can I create it in another 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

This looks like a different data type on your sql server. Creating a table of a user-defined type does not exist in the sql server. What you can do is declare a variable of this type and populate it, but you cannot create a table of this type.

Something like that...

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

In SQL Server, use the following syntax to copy a table

 SELECT * INTO newtablename FROM oldtablename; 

Answer 4

A table type is a template. You must use this object to create a table. Read-only is the only option you have.

Create proc NewT @x MyTableType to read as Select * from @x

Now you can list the columns in the instance table by calling the stored procedure. Exec newt