INSERT INTO SELECT

What is  INSERT INTO SELECT

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

INSERT INTO SELECT requires that data types in source and target tables match
The existing records in the target table are unaffected

Example:

INSERT INTO table2
SELECT * FROM table1

WHERE condition;

Select InTo

Select InTo
* it is used to copy data from one table to another.
* select data from one table and insert into new table.

Example:
Copy All column into new table.

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

Union and Union All

1. What is Union.

It is used to combine result set of two or more select statements. In union select statement contain same no of columns and datatypes.

Union operator selects only distinct value by default. if you want to select duplicate value then used union all.

It remove duplicate records.

Example:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2

Union All

This operation again allows you to join multiple data sets into one data set, but it does not remove any duplicate rows. Because this does not remove duplicate rows this process is faster then Union.

Rules to union data:

Each query must have the same number of columns
Each column must have compatible data types
Column names for the final result set are taken from the first query
ORDER BY and COMPUTE clauses can only be issued for the overall result set and not within each individual result set
GROUP BY and HAVING clauses can only be issued for each individual result set and not for the overall result set

Different types of Sql Command

Different types of Sql Command:

1. DML (Data Manipulation language)
 *Select
 *Insert
 *Update
 *Delete

2. DDL (Data definition language)
It is used to create and modify the structure of database objects in database.

*Create
*Alter
*Drop
*Truncate

3.Data Control Language.  It is used to create roles,
 permissions, and referential integrity as well it is used to control access to database by securing it.

 *GRANT – Gives user’s access privileges to database
 *REVOKE – Withdraws user’s access privileges to database given with the GRANT command

4. TCL(Transaction control language)

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

COMMIT – Saves work done in transactions
ROLLBACK – Restores database to original state since the last COMMIT command in transactions
SAVE TRANSACTION – Sets a savepoint within a transaction