How do I create a duplicate table with only the structure duplicated with a new name in SQL server 2008?
如何在SQL server 2008中创建一个只有结构与新名称重复的重复表?
I have table with 45 fields so I want to create new with same structure but new name.
我有45个字段的表,所以我想用相同的结构创建一个新的名称。
6 个解决方案
#1
38
Right click on the table in SQL Management Studio.
右键单击SQL Management Studio中的表。
Select Script... Create to... New Query Window.
选择脚本…创建……新的查询窗口。
This will generate a script to recreate the table in a new query window.
这将生成在新的查询窗口中重新创建表的脚本。
Change the name of the table in the script to whatever you want the new table to be named.
将脚本中表的名称更改为要对新表命名的任何名称。
Execute the script.
执行该脚本。
#2
22
SELECT *
INTO target
FROM source
WHERE 1 = 2
#3
2
Here, I will show you 2 different implementation:
在这里,我将展示两个不同的实现:
First:
第一:
If you just need to create a duplicate table then just run the command:
如果您只需要创建一个重复的表,那么只需运行以下命令:
SELECT top 0 * INTO [dbo].[DuplicateTable]
FROM [dbo].[MainTable]
Of course, it doesn't work completely. constraints don't get copied, nor do primary keys, or default values. The command only creates a new table with the same column structure and if you want to insert data into the new table.
当然,它并不完全有效。约束不会被复制,主键或默认值也不会被复制。该命令只创建具有相同列结构的新表,如果您希望将数据插入到新表中。
Second (recommended):
第二个(推荐):
But If you want to duplicate the table with all its constraints & keys follows this below steps:
但是,如果您想要复制表的所有约束和键,下面的步骤如下:
- Open the database in SQL Management Studio.
- 在SQL管理Studio中打开数据库。
- Right-click on the table that you want to duplicate.
- 右键单击要复制的表。
- Select Script Table as -> Create to -> New Query Editor Window. This will generate a script to recreate the table in a new query window.
- 选择脚本表为->创建到->新的查询编辑器窗口。这将生成在新的查询窗口中重新创建表的脚本。
- Change the table name and relative keys & constraints in the script.
- 更改脚本中的表名和相关键和约束。
- Execute the script.
- 执行该脚本。
#4
1
For creating a new table from existing table
用于从现有表创建新表
SELECT * INTO New_table FROM Old_Table
从Old_Table中选择*到New_table。
For Inserting Data from one table another table
用于从一个表插入数据到另一个表
Insert into Table_Name2 select top 1 * from Table_Name1
插入到Table_Name2中,从Table_Name1中选择top 1 *
#5
0
SELECT * INTO newtable FROM oldtable where 1=2
where 1=2, this statement is use when only Copy complete structure of a
table in sql without Copying data of table
SELECT * INTO newtable FROM oldtable
create table with data you can use this statement
#6
-2
My SQL Server Management Studio keeps asking me how I can make it better, I have an idea! The ability to highlight a table and then, ctrl C, ctrl V! would be great and the answer to this question at the same time!
我的SQL Server Management Studio一直在问我如何才能做得更好,我有一个主意!能够突出显示一个表,然后,ctrl C, ctrl V!很好,同时也是这个问题的答案!
#1
38
Right click on the table in SQL Management Studio.
右键单击SQL Management Studio中的表。
Select Script... Create to... New Query Window.
选择脚本…创建……新的查询窗口。
This will generate a script to recreate the table in a new query window.
这将生成在新的查询窗口中重新创建表的脚本。
Change the name of the table in the script to whatever you want the new table to be named.
将脚本中表的名称更改为要对新表命名的任何名称。
Execute the script.
执行该脚本。
#2
22
SELECT *
INTO target
FROM source
WHERE 1 = 2
#3
2
Here, I will show you 2 different implementation:
在这里,我将展示两个不同的实现:
First:
第一:
If you just need to create a duplicate table then just run the command:
如果您只需要创建一个重复的表,那么只需运行以下命令:
SELECT top 0 * INTO [dbo].[DuplicateTable]
FROM [dbo].[MainTable]
Of course, it doesn't work completely. constraints don't get copied, nor do primary keys, or default values. The command only creates a new table with the same column structure and if you want to insert data into the new table.
当然,它并不完全有效。约束不会被复制,主键或默认值也不会被复制。该命令只创建具有相同列结构的新表,如果您希望将数据插入到新表中。
Second (recommended):
第二个(推荐):
But If you want to duplicate the table with all its constraints & keys follows this below steps:
但是,如果您想要复制表的所有约束和键,下面的步骤如下:
- Open the database in SQL Management Studio.
- 在SQL管理Studio中打开数据库。
- Right-click on the table that you want to duplicate.
- 右键单击要复制的表。
- Select Script Table as -> Create to -> New Query Editor Window. This will generate a script to recreate the table in a new query window.
- 选择脚本表为->创建到->新的查询编辑器窗口。这将生成在新的查询窗口中重新创建表的脚本。
- Change the table name and relative keys & constraints in the script.
- 更改脚本中的表名和相关键和约束。
- Execute the script.
- 执行该脚本。
#4
1
For creating a new table from existing table
用于从现有表创建新表
SELECT * INTO New_table FROM Old_Table
从Old_Table中选择*到New_table。
For Inserting Data from one table another table
用于从一个表插入数据到另一个表
Insert into Table_Name2 select top 1 * from Table_Name1
插入到Table_Name2中,从Table_Name1中选择top 1 *
#5
0
SELECT * INTO newtable FROM oldtable where 1=2
where 1=2, this statement is use when only Copy complete structure of a
table in sql without Copying data of table
SELECT * INTO newtable FROM oldtable
create table with data you can use this statement
#6
-2
My SQL Server Management Studio keeps asking me how I can make it better, I have an idea! The ability to highlight a table and then, ctrl C, ctrl V! would be great and the answer to this question at the same time!
我的SQL Server Management Studio一直在问我如何才能做得更好,我有一个主意!能够突出显示一个表,然后,ctrl C, ctrl V!很好,同时也是这个问题的答案!