{"id":87251,"date":"2021-03-23T09:00:35","date_gmt":"2021-03-23T03:30:35","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=87251"},"modified":"2021-03-13T14:28:51","modified_gmt":"2021-03-13T08:58:51","slug":"sql-temporary-tables-clone-tables","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/","title":{"rendered":"SQL Temporary Tables And Clone Tables"},"content":{"rendered":"

When dealing with large amounts of data, we need to take care that the integrity and correctness of data are always taken care of. We use temporary tables to make short-lived tables, which automatically vanish once the session in which they are made terminates.<\/p>\n

Clone tables are copies of already existing tables that have all the properties of the table. We need to follow various naming conventions and rules while cloning or making a temporary table.<\/p>\n

In this tutorial, we will dive into the steps and processes we need to follow to clone a table and also how to make a temporary table.<\/p>\n

What are Temporary Tables in SQL?<\/h3>\n

Temporary tables are made when we need to access a set of given data again and again. Temporary tables automatically vanish once the session in which the temporary table is made terminates.<\/p>\n

We can delete the temporary tables manually too, by executing the delete query on the temporary table. The temporary tables we make are put in the tempdb, which is a system database.<\/p>\n

The name of the temporary table needs a hash sign (#) prefix in the earlier versions.<\/p>\n

We have two types of temporary tables:<\/p>\n

1. SQL Local Temporary Table<\/h4>\n

Local tables vanish automatically once the connection in which they are made terminates.<\/p>\n

To create local temporary tables, we need to prefix the table name with a hash sign \u2018#\u2019.<\/p>\n

But in the newer version, we don\u2019t require the hash sign.<\/p>\n

Syntax:\u00a0<\/strong><\/p>\n

 CREATE TABLE tableName(\r\ncolumnName1 varchar(50),\r\ncolumnName2  int,\r\n\u2026..);\r\n<\/pre>\n
2. SQL Global Temporary Table<\/h4>\n
Global tables are made by prefixing the table name with two hash signs i.e. \u2018##\u2019. But in the newer version, we don\u2019t need to use the hash sign.<\/p>\n
Syntax:\u00a0<\/strong><\/p>\n
CREATE TABLE ##tableName(\r\ncolumnName1 varchar(50),\r\ncolumnName2  int,\r\n\u2026..);\r\n\r\n<\/pre>\n
Steps to Create a Temporary Table in SQL<\/strong><\/h4>\n
1. Create SQL Temporary Table:
\nSyntax:\u00a0<\/strong><\/p>\n
CREATE TEMPORARY TABLE tableName(\r\ncolumnName1 varchar(50),\r\ncolumnName2  int,\r\n\u2026..);\r\n<\/pre>\n
Example:<\/strong>
\nQuery:<\/strong><\/p>\n
CREATE TEMPORARY TABLE DataFlair(\r\nName varchar(50),\r\nAge int,\r\nEmpid varchar(10)\r\n);\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"create<\/a><\/p>\n
2. Insert values into the Temporary table :
\nSyntax:<\/strong><\/p>\n
INSERT INTO tableName Values(val1,val2,val3,....);\r\n<\/pre>\n
Example:<\/strong>
\nQuery:<\/strong><\/p>\n
INSERT INTO  DataFlair VALUES('Siya',24,'A02'),\r\n('Dharm',26,'B23'),\r\n('Raghav',28,'D45'),\r\n('Naman',31,'D12'),\r\n('Richa',22,'A01');\r\nselect * from dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"insert<\/a><\/p>\n
3. Select values from Temporary table:
\nSyntax:<\/strong><\/p>\n
SELECT * FROM tableName;\r\n<\/pre>\n
Example:<\/strong>
\nQuery:<\/strong><\/p>\n
SELECT  * FROM DataFlair ORDER BY empid;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"view<\/a><\/p>\n
4. Create a Temporary Table from an existing table:
\nSyntax:<\/strong><\/p>\n
CREATE TEMPORARY TABLE temp_table_name\r\nSELECT * FROM original_table ;\r\n<\/pre>\n
Example:<\/strong>
\nQuery:<\/strong><\/p>\n
CREATE TEMPORARY TABLE DataFlair_temp1\r\nSELECT * FROM DataFlair ;\r\nSELECT * FROM dataflair_temp1;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"create<\/a><\/p>\n
Drop a Temporary Table in SQL<\/h4>\n
Syntax:<\/strong><\/p>\n
DROP TABLE tempTableName;\r\n<\/pre>\n
Example:<\/strong>
\nQuery:<\/strong><\/p>\n
DROP TABLE DataFlair_temp1;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"drop<\/a><\/p>\n
What are SQL Clone Tables?<\/h3>\n
We use clone tables when we require the table to be an exact copy in terms of index, constraints and default values, and so forth.<\/p>\n
We use clone tables to create exact copies of a table, this is beneficial when our regular Create Select statements can\u2019t yield accurate results.<\/p>\n
Steps to Clone a Table in SQL<\/h4>\n
1. Use SHOW CREATE TABLE to get all the details of the table whose clone we need to make.
\nSyntax:\u00a0<\/strong><\/p>\n
SHOW CREATE TABLE tableName;\r\n<\/pre>\n
Example:<\/strong>
\nQuery:<\/strong><\/p>\n
Show create table DataFlair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"Clone<\/a><\/p>\n
2. Modify the resultant SELECT query to the name of the clone table to get the same clone table.
\nSyntax:\u00a0<\/strong><\/p>\n
CREATE TABLE cloneTableName(\r\ncolName1 datatype,\r\ncolName2 datatype,\r\n\u2026.);\r\n<\/pre>\n
Example:<\/strong>
\nQuery:<\/strong><\/p>\n
CREATE TEMPORARY TABLE dataflair_clone (  `Name` varchar(50) DEFAULT NULL,  `Age` int(11) DEFAULT NULL,\r\n`Empid` varchar(10) DEFAULT NULL);\r\nSELECT * FROM DataFlair_clone;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
3. If we need the data as well then we can use the INSERT INTO statement to get the data from the original table.
\nSyntax:<\/strong><\/p>\n
INSERT INTO tableNameClone (col1,col2,....) \r\nSELECT  col1,col2,... FROM tableName;\r\n<\/pre>\n
Example:<\/strong>
\nQuery:<\/strong><\/p>\n
INSERT INTO DataFlair_clone (Name,Age,Empid) SELECT  Name , Age, Empid FROM DataFlair;\r\nSELECT * FROM DataFlair_clone;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"Clone<\/a><\/p>\n
Drop a Clone Table in SQL<\/h4>\n
Syntax:\u00a0<\/strong><\/p>\n
DROP TABLE tableName;\r\n<\/pre>\n
Example:<\/strong>
\nQuery:\u00a0<\/strong><\/p>\n
DROP TABLE DataFlair_clone;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"drop<\/a><\/p>\n
Difference Between SQL Temporary Tables and Clone Tables<\/h3>\n
Differences between Temporary and Clone tables are as follows:<\/p>\n
    \n
  1. Temporary tables are made in the tempdb, whereas clone tables are made in the memory.<\/li>\n
  2. Temporary tables vanish once the connection in which they are made vanishes while clone tables remain in memory until they are manually dropped.<\/li>\n
  3. SQL Temporary tables are a subset of the original table, whereas clone tables are the exact copy of the original table.<\/li>\n
  4. Temporary tables allow index and constraint modification while this is not permissible in clone tables.<\/li>\n
  5. Temporary tables can\u2019t be put in transactions, whereas clone tables can be put in transactions.<\/li>\n
  6. SQL Temporary tables fasten the queries while clone tables create no such difference.<\/li>\n<\/ol>\n
    Summary<\/h3>\n
    In this article, we have understood in detail about the clone table and temporary table in SQL. Temporary tables are handy when we have a large amount of data, but we need only a small part of it for use.<\/p>\n
    Whereas clone tables come in handy when we require the same table to perform sensitive transactions and chances of failures are there.<\/p>\n
    The use of temporary and clone tables ensures that our data remains safe in all conditions whatsoever.<\/p>\n","protected":false},"excerpt":{"rendered":"
    When dealing with large amounts of data, we need to take care that the integrity and correctness of data are always taken care of. We use temporary tables to make short-lived tables, which automatically...<\/p>\n","protected":false},"author":1,"featured_media":87252,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[23857,23856,23858],"class_list":["post-87251","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-sql-clone-tables","tag-sql-temporary-tables","tag-temporary-tables-vs-clone-tables-in-sql"],"yoast_head":"\nSQL Temporary Tables And Clone Tables - DataFlair<\/title>\n<meta name=\"description\" content=\"Temporary table in SQL Server is a database table that exists temporarily on the database server. Learn more about Clone and temporary tables\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Temporary Tables And Clone Tables - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Temporary table in SQL Server is a database table that exists temporarily on the database server. Learn more about Clone and temporary tables\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2021-03-23T03:30:35+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/Temporary-Tables-Clone-Tables-in-SQL.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Temporary Tables And Clone Tables - DataFlair","description":"Temporary table in SQL Server is a database table that exists temporarily on the database server. Learn more about Clone and temporary tables","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/","og_locale":"en_US","og_type":"article","og_title":"SQL Temporary Tables And Clone Tables - DataFlair","og_description":"Temporary table in SQL Server is a database table that exists temporarily on the database server. Learn more about Clone and temporary tables","og_url":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2021-03-23T03:30:35+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/Temporary-Tables-Clone-Tables-in-SQL.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/b49855299264df5e27e3ec6c2cd9fde9"},"headline":"SQL Temporary Tables And Clone Tables","datePublished":"2021-03-23T03:30:35+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/"},"wordCount":681,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/Temporary-Tables-Clone-Tables-in-SQL.jpg","keywords":["SQL CLone Tables","SQL Temporary Tables","temporary tables vs clone tables in SQL"],"articleSection":["SQL Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/","url":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/","name":"SQL Temporary Tables And Clone Tables - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/Temporary-Tables-Clone-Tables-in-SQL.jpg","datePublished":"2021-03-23T03:30:35+00:00","description":"Temporary table in SQL Server is a database table that exists temporarily on the database server. Learn more about Clone and temporary tables","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/Temporary-Tables-Clone-Tables-in-SQL.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/Temporary-Tables-Clone-Tables-in-SQL.jpg","width":1200,"height":628,"caption":"Temporary Tables & Clone Tables in SQL"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sql-temporary-tables-clone-tables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"SQL Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sql\/"},{"@type":"ListItem","position":3,"name":"SQL Temporary Tables And Clone Tables"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/b49855299264df5e27e3ec6c2cd9fde9","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/ef46b745ddad2fad690af626c6ef29b91809ad0a9f5ef398d07817d8cad042f5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/ef46b745ddad2fad690af626c6ef29b91809ad0a9f5ef398d07817d8cad042f5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ef46b745ddad2fad690af626c6ef29b91809ad0a9f5ef398d07817d8cad042f5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"DataFlair Team is a group of passionate educators and industry experts dedicated to providing high-quality online learning resources on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. With years of experience in the field, the team aims to simplify complex topics and help learners advance their careers. At DataFlair, we believe in empowering students and professionals with the knowledge and skills needed to thrive in today\u2019s fast-paced tech industry. Follow us for Free courses, expert insights, tutorials, and practical tips to boost your learning journey.","url":"https:\/\/data-flair.training\/blogs\/author\/datafbdad\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/87251","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=87251"}],"version-history":[{"count":1,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/87251\/revisions"}],"predecessor-version":[{"id":87262,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/87251\/revisions\/87262"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/87252"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=87251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=87251"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=87251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}