基于用户定义的类型创建 SQL Server 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22390358/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
create SQL Server table based on a user defined type
提问by gt6989b
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 answerthe suggestion was to try
我想创建一个这种类型的表。从这个答案中，建议是尝试
CREATE TABLE dbo.MyNewTable AS dbo.MyTableType
which produced the following error message in my SQL Server Express 2012:
这在我的 SQL Server Express 2012 中产生了以下错误消息：
Incorrect syntax near the keyword 'OF'.
关键字“OF”附近的语法不正确。
Is this not supported by SQL Server Express? If so, could I create it some other way, for example using DECLARE?
SQL Server Express 不支持此功能吗？如果是这样，我可以通过其他方式创建它，例如使用DECLARE?
回答by Dave Mason
--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
回答by M.Ali
It is just like an other datetype in your sql server. Creating a Table of a user defined type there is no such thing in sql server. What you can do is Declare a variable of this type and populate it but you cant create a table of this type.
它就像 sql server 中的其他日期类型。创建用户定义类型的表在 sql server 中没有这样的东西。你可以做的是声明一个这种类型的变量并填充它，但你不能创建一个这种类型的表。
Something like this...
像这样的东西...
/* Declare a variable of this type */
DECLARE @My_Table_Var AS dbo.MyTableType;
/* Populate the table with data */
INSERT INTO @My_Table_Var
SELECT Col1, Col2, Col3 ,.....
FROM Source_Table
回答by user2063329
Table type is a template. You need to use this object to create a table. The readonly is the only option you have.
表类型是一个模板。您需要使用此对象来创建表。readonly 是您唯一的选择。
Create proc NewT @x MyTableType readonly as Select * from @x
创建 proc NewT @x MyTableType 只读为 Select * from @x
Now you can list the columns in the instantiated table calling the stored procedure. Exec NewT
现在您可以列出调用存储过程的实例化表中的列。执行新T
回答by VJ Hil
IN sql server use the following syntax to copy the table
IN sql server 使用以下语法复制表
SELECT * INTO newtablename FROM oldtablename;
