根据用户定义的类型创建SQL Server表 [英] create SQL Server table based on a user defined type

问题描述

我使用以下创建的已定义表类型

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 the suggestion was to try

CREATE TABLE dbo.MyNewTable AS dbo.MyTableType

这会产生以下错误消息在我的SQL Server Express 2012中：

which produced the following error message in my SQL Server Express 2012:

关键字'OF'附近的语法不正确。

Incorrect syntax near the keyword 'OF'.

SQL Server Express不支持吗？如果是这样，我可以用其他方式创建它，例如使用 DECLARE 吗？

Is this not supported by SQL Server Express? If so, could I create it some other way, for example using DECLARE?

推荐答案

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

这篇关于根据用户定义的类型创建SQL Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！