creating a table based on a user defined tabletype

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

creating a table based on a user defined tabletype

Author

Topic

gt6989b
Starting Member

1 Post

Posted - 2014-03-13 : 17:23:36

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 (http://stackoverflow.com/a/2432347/399573) 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`?[url][/url][url][/url]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-13 : 18:23:56

That answer from StackOverflow is not a SQL Server solution. There might be another way, but here is one way that works:

DECLARE @Foo AS dbo.MyTableType
 

SELECT *
INTO MyNewTable
FROM @Foo

SELECT *
FROM MyNewTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources