创建sql表的副本

I need a query to create a table which is the exact replica but with different table name and without any data from the source table using a sql query!

我需要一个查询来创建一个表,它是一个精确的副本,但具有不同的表名,并且使用sql查询没有来自源表的任何数据!

10 个解决方案

#1

You can try this

你可以试试这个

SELECT * INTO Table_Copy
FROM Table
where 1=2

It will create a empty table with the same structure.

它将创建一个具有相同结构的空表。

#2

Jonathan has it (upvoted), and you should probably go with that because it's more portable. I normally use something similar:

Jonathan拥有它(upvoted),你应该选择它,因为它更便携。我通常使用类似的东西:

SELECT TOP 0 * INTO [New_Table] FROM [Old_Table]

I think this better expresses what you're doing, but I like Jonathan's because 'TOP 0' is SQL Server specific, and so his is more portable.

我认为这更好地表达了你正在做的事情,但我喜欢Jonathan的,因为'TOP 0'是特定于SQL Server的,因此他更便携。

#3

SQL Server Management Studio

SQL Server Management Studio

Object Explorer
Connect -> Your server

连接 - >您的服务器

Databases -> Choose Database

数据库 - >选择数据库

Tables
Right Click Your Table

右键单击您的表

Script Table as -> Create To -> New Query Editor Window

脚本表为 - >创建到 - >新查询编辑器窗口

#4

For MySQL, you can call SHOW CREATE TABLE table_name;

对于MySQL,可以调用SHOW CREATE TABLE table_name;

It will display a CREATE TABLE query. Simply change the table name in that query and you're good to go.

它将显示CREATE TABLE查询。只需更改该查询中的表名即可。

http://dev.mysql.com/doc/refman/5.1/en/show-create-table.html

#5

If you use Postgresql:

如果你使用Postgresql:

CREATE TABLE LIKE table_name

http://www.postgresql.org/docs/8.1/static/sql-createtable.html

#6

SELECT * INTO Table_Copy
FROM Table
where 1=2

This worked very well, when i tried to create a replica of the table without any data's.

当我尝试创建没有任何数据的表的副本时,这非常有效。

SELECT * INTO Table_Copy
FROM Table

This will create a replica with the data's too.

这将创建一个包含数据的副本。

#7

This can help you:

这可以帮助您:

CREATE TABLE foo AS SELECT...

#8

select * into newtablename from sourcetablename
go
truncate newtablename
go

That will result in an exact copy but it also copies the data at first which you remove with the truncate statement.

这将导致精确的副本,但它也会首先复制您使用truncate语句删除的数据。

#9

create table <new table name> as select * from <old tale name from which you would like to extract data>

将表 <新表名> 创建为select * from

It will create a new table with a different name but will copy all existing data from the old table to new table.

它将创建一个具有不同名称的新表,但会将旧表中的所有现有数据复制到新表。

#10

in postgres you can use INHERITS or LIKE keyword to make replica of a table(only copies structure of the table)

在postgres中,您可以使用INHERITS或LIKE关键字来制作表的副本(仅复制表的结构)

CREATE TABLE client_new (LIKE client);

CREATE TABLE client_new(LIKE客户端);

CREATE TABLE client_new () INHERITS (client)

CREATE TABLE client_new()INHERITS(客户端)

Use of INHERITS creates a persistent relationship between the new child table and its parent table(s). Schema modifications to the parent(s) normally propagate to children as well, and by default the data of the child table is included in scans of the parent(s). LIKE clause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints.Unlike INHERITS, the new table and original table are completely decoupled after creation is complete. Changes to the original table will not be applied to the new table, and it is not possible to include data of the new table in scans of the original table.

使用INHERITS会在新子表与其父表之间创建持久关系。对父项的模式修改通常也传播给子项,默认情况下,子表的数据包含在父项的扫描中。 LIKE子句指定一个表,新表自动复制所有列名称,数据类型及其非空约束。与INHERITS类似,新表和原始表在创建完成后完全解耦。对原始表的更改将不会应用于新表,并且无法在原始表的扫描中包含新表的数据。

#1