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?

score 29 · Accepted Answer · answered Mar 13 '14 at 21:10

29

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

answered Mar 13 '14 at 21:10

Dave Mason

4,746
2
23
24

3

WARNING: this gets you 9 yards, but DRI is not included (Primary Key, etc) – Stan Jul 02 '15 at 13:43
@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. – jimhark May 13 '16 at 00:53
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 – ColinMac Aug 03 '18 at 16:37
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. – Nabakamal Das Feb 13 '19 at 09:59

score 5 · Answer 2 · answered Mar 13 '14 at 20:51

5

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

answered Mar 13 '14 at 20:51

M.Ali

67,945
13
101
127

1

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... – gt6989b Mar 13 '14 at 20:58
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. – M.Ali Mar 13 '14 at 21:10
How about this answer http://stackoverflow.com/a/22390997/399573? Granted it's a work around... I'm upvoting yours as well. – gt6989b Mar 13 '14 at 21:13
What is the different in this answer and my answer ? – M.Ali Mar 13 '14 at 21:14
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. – gt6989b Mar 13 '14 at 21:20
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. – M.Ali Mar 13 '14 at 21:28
Either way, I am not saying you were **wrong**, I immediately mentioned it was a workaround, but still it does what I need done. – gt6989b Mar 13 '14 at 21:30
1

Anyway glad you have found what you were looking for :) Peace out :) – M.Ali Mar 13 '14 at 21:33
I appreciate your help -- the discussion was very helpful to me as well. Thank you very much. – gt6989b Mar 13 '14 at 21:48

score 0 · Answer 3 · answered Mar 13 '14 at 21:32

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

Bryan Williams · Answer 4 · 2020-06-12T17:01:11.790

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.

score -2 · Answer 5 · edited Mar 13 '14 at 20:43

-2

IN sql server use the following syntax to copy the table

SELECT * INTO newtablename FROM oldtablename;

edited Mar 13 '14 at 20:43

Mihai

26,325
7
66
81

answered Mar 13 '14 at 20:42

VJ Hil

904
6
15

2

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. – gt6989b Mar 13 '14 at 20:44

create SQL Server table based on a user defined type

5 Answers5

Linked