CODEWITHSUNDEEP
sqlsql-serversql-server-2008
quant
quant

Reputation: 23172

How do I create a temporary table from a type?

Suppose I have some type:

CREATE TYPE usr.NameList AS TABLE ([name] VARCHAR(MAX) NOT NULL);

And now I want to create a temporary table based on this type, how do I do it?

CREATE TABLE #superBrand (usr.NameList) -- doesn't work

Also, from this: create table based on a user defined type:

CREATE TABLE #superBrand OF usr.NameList -- doesn't work

Upvotes: 16

Views: 19682

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

Pure.Krome's answer shows how to use a table variable instead of a #temp table. If you really want a #temp table based on a known table type (without having to know the column names/definitions), you can say:

DECLARE @d usr.NameList;
SELECT * INTO #superBrand FROM @d;

Now, #superBrand should match the table structure of the table type, minus the constraints (and marginally useful secondary indexes, starting with SQL Server 2014).

Of course, the rest of your code that then populates the #temp table is going to have to know the structure. So, what exactly is the purpose of declaring a #temp table with the same structure as a table type?

Upvotes: 26

Pure.Krome
Pure.Krome

Reputation: 87087

/****** Object:  UserDefinedTableType [dbo].[IdentityType] ******/
CREATE TYPE [dbo].[IdentityType] AS TABLE(
    [Id] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO




DECLARE @IdTable [dbo].[IdentityType] 
INSERT INTO @IdTable VALUES(1)

Upvotes: -1

Related Questions