如何基于另一个表创建一个表

2021-12-13 00:00:00 sql-server-2008 sql-server create-table

我想根据另一个表的定义创建一个表.

I want to create a table based on the definition of another table.

我来自 oracle，我通常会这样做:

I'm coming from oracle and I'd normally do this:

CREATE TABLE schema.newtable AS SELECT * FROM schema.oldtable;

我似乎无法在 SQL Server 2008 中执行此操作.

I can't seem to be able to do this in SQL Server 2008.

推荐答案

SQL Server 中没有这样的语法，尽管 CREATE TABLE AS ... SELECT 在 PDW 中确实存在.在 SQL Server 中，您可以使用此查询创建一个空表:

There is no such syntax in SQL Server, though CREATE TABLE AS ... SELECT does exist in PDW. In SQL Server you can use this query to create an empty table:

SELECT * INTO schema.newtable FROM schema.oldtable WHERE 1 = 0;

(如果你想制作一个表的副本包括所有的数据，那么省略WHERE子句.)

(If you want to make a copy of the table including all of the data, then leave out the WHERE clause.)

请注意，这会创建相同的列结构(包括 IDENTITY 列，如果存在)，但不会复制任何索引、约束、触发器等.

Note that this creates the same column structure (including an IDENTITY column if one exists) but it does not copy any indexes, constraints, triggers, etc.

相关文章