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
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With