SQL Insert options (Single table insert and Multi Table Insert)

In This article I'm going to explain  SINGLE TABLE INSERT And Multi Table Insert.

As we all know basic command of Insert is:

INSERT INTO table_name (column_names) VALUES (columns_values);

Note: The column name must be sett ed. If we want to give NULL value then we would leave empty or we should write NULL at the end

For Example;

INSERT INTO dept (department_id, department_name, manager_id, location_id)                                                            VALUES (300, ‘Engineering’, 150, 1600);

INSERT INTO dept (department_id, department_name, manager_id, location_id)                                                           VALUES (205, ‘Engineering’, NULL, NULL);

INSERT INTO dept (department_id, department_name) VALUES (200, ‘Engineering’);

 An other option insert is Multi row Insert. This option provide us to Insert an other table or more than one.

INSERT INTO table_name(1)  SELECT column_name FROM  table_name(2) WHERE requires……..;

 For Example;

INSERT INTO sales_reps(id, name, salary, commission_pct)

SELECT employee_id, last_name, salary, commission_pct

FROM   employees

WHERE job_id LIKE ‘%REP%’;

MULTI TABLE INSERT

This option provide us to insert Multi table So, we have 2 insert section

• Conditional Insert
• Unconditional Insert

UNCONDITIONAL INSERT

The insert will be write at the same time and same values to the tables without any conditions and any limits. Here insert command doesn't loot what is column name or table name.

INSERT ALL
INTO Table_name(1) VALUES(column_names(1))
INTO table_name(2) VALUES(column_names (2))

SELECT column_names (3)  FROM  Table_name(3)
WHERE requires ……………..;

Note: At 1 and 2 column_names should be the same with 3 Selection

Consider if in Select we get 30 answers then insert will write 30 answers to 1 and 2 tables. 

For example;

INSERT ALL
INTO Sal_history VALUES (EMPID, HIREDATE, SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)

SELECT employee_id EMPID, hire_date HIREDATE,
salary SAL, manager_id MGR

FROM employees
WHERE employee_id > 100;

CONDITIONAL INSERT

Conditional Insert Also Have two options

• FIRST
• ALL

INSERT ALL

Its look like multi table insert  UNCONDITIONAL INSERT, but INSERT ALL have a condition;

Lets look at the command.

INSERT ALL

WHEN condition(1) THEN

INTO Table_name(1) VALUES(column_name(1))

WHEN condition (2) THEN

INTO Table_name (2) VALUES(column_name(2))

SELECT column_name(3) FROM Table_name(3)

WHERE condition(3)………………….;

For example;

INSERT ALL

WHEN HIREDATE < ’10-FEB-13′ THEN

INTO emp_history VALUES (EMPID, HIREDATE, SAL)

WHEN COMM IS NOT NULL THEN

INTO emp_sales VALUES (EMPID,COMM,SAL)

SELECT employee_id EMPID, hire_date HIREDATE,

salary SAL, commission_pct COMM

FROM  employees;

INSERT FIRST

At this option Insert will keep going until get the first condition in case it will write at first Select table, it doesn't write any others table ;

INSERT FIRST

WHEN condition(1) THEN

INTO Table_name(1) VALUES (column_name(1))

WHEN condition(2) THEN

INTO Table_name(2) VALUES (column_name(2))

ELSE

INTO Table_name(3) VALUES (column_name(3))

SELECT column_name(4)

FROM Table_name(4) WHERE condition(4)…………………;

For example ;

INSERT FIRST

WHEN salary < 4000 THEN

INTO sal_low VALUES (employee_id, last_name, salary)

WHEN salary between 4000 and 10000 THEN

INTO sal_mid VALUES (employee_id, last_name, salary)

ELSE

INTO sal_high VALUES (employee_id, last_name, salary)

SELECT employee_id, last_name, salary

FROM employees;