14

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
Community
  • 1
  • 1
quant
  • 21,507
  • 32
  • 115
  • 211

2 Answers2

23

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?

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 1
    Well I use the type as a parameter for a procedure, and I also want to use it to declare some of the tables that will be created. It's essentially a shortcut to avoid having to redefine the types. Is there a better way? – quant Jan 15 '14 at 04:07
  • Note that this way will result in recompiles because the temp table was not statically declared. – Mark Sowul Jul 19 '14 at 15:21
  • This fails static code analysis. "ABC.PROC.SQL(11,9,11,9): StaticCodeAnalysis error : SR0001 : Microsoft.Rules.Data : The shape of the result set produced by a SELECT * statement will change if the underlying table or view structure changes." – Koshimitsu Oct 07 '14 at 22:56
  • 1
    @Koshimitsu So? You need to pick what you want: the ability to create a #temp table with the structure based on the "right now" version of the table, or passing all static code analysis. You can't really have both, by definition. My guess is this OP cares more about the former than the latter, but if you think you can provide a solution that fully meets the requirements in this question *and* passes static code analysis, by all means, please post an answer. – Aaron Bertrand Oct 07 '14 at 23:19
-2
/****** 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)
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647