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?

Subserve

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

Laureen

WARNING: this gets you 9 yards, but DRI is not included (Primary Key, etc) – Inhaler

@Stan, I understand what you're saying, there are limitations. But if you're on the 5 yard line, 9 yards is plenty. (My apologies to non-fans of American Football for continuing the sports anology.) I'm going to try to use this to create temporary tables that I already have types for. Should shave some code duplication. – Pulliam

This solves my issue... but I'd like to create the table (of the custom type) directly. Is that possible? e.g.: create table dbo.my_table custom_type – Easel

Great answer. One point that I'd like to add - if you wanna create the table (from the UDT type in DB1) in another database (DB2) then the table name specified after the "INTO" clause should be modified to specify the same i.e. fully qualified. Eg. "INTO DB2.dbo.NewTableInDB2". Thanks. – Odaniel

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

Fleabag

I surely hope there is a better answer than 'there is no such thing' - sounds like a very intuitive task to do. But if not, I'll be happy to both upvote and accept... – Subserve

I am pretty darn sure there is no such functionality as Creating a table of a user defined type or even any type. A table is an object of table type. These User-defined table types were introduced in sql server 2008 mainly to aid the need of passing table values to stored procedures. And this is the main use of these user defined table types. If you want to pass a stored procedure a table to process this is the way to go. Create a table type. Make your procedure to accept a parameter of that table type and in this way you can pass a table as a parameter to your procedures. – Fleabag

How about this answer https://mcmap.net/q/531161/-create-sql-server-table-based-on-a-user-defined-type? Granted it's a work around... I'm upvoting yours as well. – Subserve

What is the different in this answer and my answer ? – Fleabag

You rely on existence of Source_Table and create a temporary variable. He does not rely on anything and creates a dbo.NewTable as I specifically requested. In other words, he does exactly what I wanted - creates a table based on a type, whereas you (a) claim that it is impossible and (b) give example of creating a variable from a type. – Subserve

I claimed that you cannot CREATE a TABLE of ANY TYPE, NEITHER of any USER Defined type. In this answer OP Declare a table of your table type and rather then populating that table he Created a table of same schema. There is a difference in an object of a type and a type being a table. – Fleabag

Either way, I am not saying you were wrong, I immediately mentioned it was a workaround, but still it does what I need done. – Subserve

Anyway glad you have found what you were looking for :) Peace out :) – Fleabag

I appreciate your help -- the discussion was very helpful to me as well. Thank you very much. – Subserve

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

Smoky

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.

Garboard

Answer 5

IN sql server use the following syntax to copy the table

SELECT * INTO newtablename FROM oldtablename;

Morel

I am not talking about creating a table based on a table, rather creating a table, based on a table type. There is nothing to select from. – Subserve