Create SQL Server Table Based on a User Defined Type

create SQL Server table based on a user defined type

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

create table based on a user defined type

Have you tried:

create table test_type_table of test_type;

Is it possible to use user defined table type inside another user defined table type in sql

No. Why would it be? That's not how SQL Server (or any relational database, for that matter) work.

From TechNet's page on User-Defined Table Types:

Restrictions

User-defined table types have the following restrictions:

• A user-defined table type cannot be used as a column in a table or a
  field in a structured user-defined type.

"Nesting" in relational databases are achieved by using Foreign keys

You can't even create a foreign key constraint between two user defined table types.

What you can do is create two table types that one of them have a column to keep the id of the other, like this:

CREATE TYPE A AS TABLE
(
    A_Id int
)

GO

CREATE TYPE B AS TABLE
(
    B_Id int,
    A_Id int -- "FK"
)
GO

Create an Alias to a user defined table type in a different schema

Quote from comment on the original question:

Alias data types are for scalar data types, you can't create an alias data type of a table type. You'll need to give explicit access to the types on the schema here so that the USER's can declare a parameter of the type. – 

User: Larnu

He gave me a couple of keywords which helped with my google search.

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-type-permissions-transact-sql?view=sql-server-ver15

It is still weird though, as I had allow execution and after giving them rights on the specific stored procedure, I could then remove the rights and it works. Trying to remove the rights first results in the execution failing.

---

Related Topics