In a relational database, sometimes there is a need to copy a table in SQL. This post talks about several different scenarios on doing this, and how to use SQL to accomplish each scenario.<\/p>\n
To copy a table with all the data, simply create a new table and populate the table with SELECT * from the original table. This will copy over the table structure as well as all the data that was in the original table.<\/p>\n
The syntax you would use is<\/p>\n
CREATE TABLE TABLE_NAME_2
\nSELECT * FROM TABLE_NAME 1;<\/p>\n
Please note that this will not copy over the constraints<\/a> or indexes<\/a> associated with this table.<\/p>\n <\/p>\n To copy the table structure without the data, the most generic way is to use the WHERE statement<\/a> to ensure that no rows get copied over. An example would be something like the following:<\/p>\n CREATE TABLE TABLE_NAME_1 Since 1=0 is always false, no data will be copied over.<\/p>\n The advantage of the above method is that it can be applied to all relational databases. There are additional methods you can use for each RDBMS to ensure that only the structure gets copied. For example, in MySQL, you can use the LIMIT<\/a> keyword so that no data gets copied:<\/p>\n CREATE TABLE TABLE_NAME_2 Sometimes you may only want to copy over some of the columns instead of all of the columns. In this case, instead of using SELECT *, you would select just the columns you want to copy over.<\/p>\n","protected":false},"excerpt":{"rendered":" Describes how to copy a table in a relational database using SQL. You can either copy just the table structure, or the table structure and the data.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[247],"tags":[246,245],"_links":{"self":[{"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/posts\/408"}],"collection":[{"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/comments?post=408"}],"version-history":[{"count":15,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/posts\/408\/revisions"}],"predecessor-version":[{"id":423,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/posts\/408\/revisions\/423"}],"wp:attachment":[{"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/media?parent=408"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/categories?post=408"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/tags?post=408"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Copy just table structure (no data)<\/h3>\n
\nSELECT * FROM TABLE_NAME_2
\nWHERE 1=0;<\/p>\n
\nSELECT * FROM TABLE_NAME_1 LIMIT 0<\/p>\nCopy some of the columns<\/h3>\n