SQL TABLE

SQL TABLE

Structured Query Language (SQL) is a relational database (RDBMS) where data is stored in the form of tables, that is, in rows and columns. These tables are known as tuples, where each row in the table is cited as a tuple. There are certain operations that can be carried out on these SQL tables. Some of them are listed below.

SQL TEMP TABLE

The temporary table concept was initiated in the SQL server. Developers use temporary tables, and it helps them in a number of ways.

Temporary tables can perform all types of operations that a normal table is capable of doing. These tables are generated in the tempdb database, and they can be developed during runtime.

The notion of temporary tables is only supported by MySQL versions 3.23 and above. However, in older versions, there was the concept of heap tables.

Temporary tables can be diverged into two types based on their behaviour and scope.

1. Local temporary table
2. Global temporary table

Local temporary table

A Local Temporary Table is available during the present connection time only, and they are deleted automatically once the user disconnects. This type of table is started with a hash (#) symbol.

CREATE TABLE #local table (
 user_id int,
 user_name varchar (100),
 user_addrs varchar (150)
 ); 

The following is an instance of generating a local temporary table.

Global temporary table

A Global temporary table is initiated by a double hash (##) symbol. This type of table does not get deleted and is present for all users. It behaves like a permanent table.

CREATE TABLE #global table (
 user_id int,
 user_name varchar (100),
 user_addrs varchar (150)
 ); 

The following is an instance of generating a global temporary table.

Deleting temporary table

A temporary table can be deleted in two ways. It can be deleted automatically as well as manually.

A local temporary table is inevitably deleted immediately after the user disconnects from the server.

The temporary table can be deleted manually as well by using the DROP TABLE command.

DROP TABLE #tablename

The following is the instance for deleting a temporary table. It is the same as deleting a regular table.

SQL CLONE TABLE

It is possible to replicate or clone one table from another SQL table in the same server. This is done by making use of the SELECT statement.

SELECT *
 INTO <new_table>
 FROM <old_table>; 

The following is the syntax for generating the copy of one table from another table.

The above statement will copy all the content of the old table into the new table.

The following is the syntax to copy specific columns from the old table to the new table.

SELECT column1, column2, column3, …
 INTO <new_table>
 FROM <old_table>; 

The WHERE clause can also be used with the above statements, and certain conditions can be specified as well. Also, new column names can be given using the AS clause.

Example:

Let’s consider the following Source_table.

ID	Fname	Lname	ProjectID	Email	Profile	City
1	Harry	Kane	A1	harry@abc.com	SES	Kolkata
2	Ron	Wesley	B2	ron@efg.com	SDE	Mumbai
3	Dobby	Sane	C3	dobby@hij.com	SDE	Pune
4	Albus	Dolby	D4	albus@klm.com	HR	Agra
5	Snape	Wright	E5	snape@nop.com	SDE	Delhi

Query:

CREATE TABLE Contact LIKE Source_table;

The following query will create an empty structure with the same attributes as that of the Source_table.

Output:

ID

Fname

Lname

ProjectID

Email

Profile

City

Query:

INSERT INTO Contact SELECT *
 FROM Source_table; 

Now, the following query will clone all the contents of the Source_table into the new Contact table.

Output:

ID	Fname	Lname	ProjectID	Email	Profile	City
1	Harry	Kane	A1	harry@abc.com	SES	Kolkata
2	Ron	Wesley	B2	ron@efg.com	SDE	Mumbai
3	Dobby	Sane	C3	dobby@hij.com	SDE	Pune
4	Albus	Dolby	D4	albus@klm.com	HR	Agra
5	Snape	Wright	E5	snape@nop.com	SDE	Delhi

Creating a clone table helps in various database operations like testing since this table does not affect the records of the original table. Hence, the data in the original table remains intact.

These are some of the advanced SQL TABLE statements which are used by developers to deal with the tables in the database. These statements are essential and should be handled carefully.

These statements make database operations like testing, storing data temporarily and all much easier and faster.