CODEWITHSUNDEEP
sqlsql-server
Jaison
Jaison

Reputation:

Create a replica of a sql table

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!

Upvotes: 4

Views: 10531

Answers (11)

piyush gohel
piyush gohel

Reputation: 11

Can be created by trigger 3 triggers can be created for insert,update and delete operation

Upvotes: 1

daemonThread
daemonThread

Reputation: 1044

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

CREATE TABLE client_new (LIKE client);

or

CREATE TABLE client_new () INHERITS (client)

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.

Upvotes: 0

user2537541
user2537541

Reputation: 1

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

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

Upvotes: 0

pavithra
pavithra

Reputation: 11

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.

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

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

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.

Upvotes: 4

Peter
Peter

Reputation: 14518

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.

Upvotes: 0

Jonathan
Jonathan

Reputation: 12015

You can try this

SELECT * INTO Table_Copy
FROM Table
where 1=2

It will create a empty table with the same structure.

Upvotes: 11

Strae
Strae

Reputation: 19465

If you use Postgresql:

CREATE TABLE LIKE table_name

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

Upvotes: 1

Jacob Lauzier
Jacob Lauzier

Reputation: 875

For MySQL, you can call 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.

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

Upvotes: 1

Artem Barger
Artem Barger

Reputation: 41232

This can help you:

CREATE TABLE foo AS SELECT...

Read more here

Upvotes: 0

Tom Ritter
Tom Ritter

Reputation: 101400

  • 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

Upvotes: 5

Related Questions