SQL Tutorials

SQL – Insert

Inserting a new row into the dept table

insert into dept (deptno, dname, loc) values (10,'Apple','Boston')

You can omit the column list in the insert statement but then you have to enter those in the same order as they appear in the table and you have o include all the columns in the values.

The following example illustrates the use of the "default" keyword while inserting the records.

insert into table_name values(default); or insert into table_name(column1,column2..) values(default)

How to create a new table having the same structure as some other table?

create table new_table as select *

from old_table

How to insert data into multiple tables?

There are two terms used INSERT ALL AND INSERT FIRST

Example:

Suppose there are three tables emp,emp_1 and emp_2 with the same sructure and columns.

insert all

when sal in (800,1600) then

into emp (empno,ename,job) values (empno,ename,job)

when sal = 3200 then

into emp_1 (empno,ename,job) values (empno,ename,job)

else

p> into emp_2 (empno,ename,job) values (empno,ename,job)

select empno,ename,job

from emp

INSERT FIRST will breakout as soon as it sees a condition that evaluates to true.

INSERT ALL will evaluate al the conditions even if the previous condition evluates to true.