Multiple INSERT, INSERT ALL , INSERT WHEN, INSERT INTO RETURNING INTO


In this post I would like to explain SQL INSERT usages.

First we are starting with a simple INSERT clause ;

Create 2 simple tables to insert data in them;

CREATE TABLE TESTTABLE1

(

   COLUMN1 VARCHAR2(20),

   COLUMN2 VARCHAR2(20)

);

CREATE TABLE TESTTABLE2

(

  COLUMN1 VARCHAR2(20),

  COLUMN2 VARCHAR2(20),

  COLUMN3 VARCHAR2(20)

);

CREATE TABLE succeeded.

CREATE TABLE succeeded.

Here we can see simple SQL Insert;

–Simple insert

INSERT INTO TESTTABLE1 (column1,column2) VALUES (‘test1′,’test1’);

–We can insert without specifying column names

INSERT INTO TESTTABLE1 VALUES (‘test2′,’test2’);

SELECT * FROM TESTTABLE1;

COLUMN1              COLUMN2

——————– ——————–

test1                test1

test2                test2

2 rows selected

Now I want to demonstrate INSERT ALL usage; With the syntax below you can insert multiple values to different tables at the same time;

TRUNCATE TABLE TESTTABLE1; –Clear all table content

TRUNCATE TABLE TESTTABLE2;

INSERT ALL

INTO TESTTABLE1 (column1,column2) — TESTTABLE1 is used

VALUES (‘Test1′,’Test1’)

INTO TESTTABLE2 (column1,column2,column3) — TESTTABLE2 is used

VALUES (‘Test2′,’Test2′,’Test2’)

INTO TESTTABLE2(column1,column2,column3) — again TESTTABLE2 is used

VALUES (‘Test2′,’Test2′,’Test2’)

SELECT * FROM DUAL; — a dummy select is required here for the syntax

–after that

SELECT * FROM TESTTABLE1;

COLUMN1              COLUMN2

——————– ——————–

Test1                Test1

1 rows selected

SELECT * FROM TESTTABLE2;

COLUMN1              COLUMN2              COLUMN3

——————– ——————– ——————–

Test2                Test2                Test2

Test2                Test2                Test2

2 rows selected

— as you can see we can insert into different tables

We can use WHEN THEN clause in INSERT SQL

–first here is regions data in regions table

SELECT * FROM REGIONS;

REGION_ID              REGION_NAME

———————- ————————-

1                      Europe

2                      Americas

3                      Asia

4                      Middle East and Africa

4 rows selected

–Then we can use INSERT ALL with WHEN

–create an empty table

CREATE TABLE TESTTABLE3

(

 COLUMN1 VARCHAR2(20),

 COLUMN2 VARCHAR2(20),

 COLUMN3 VARCHAR2(20)

);

CREATE TABLE succeeded.

INSERT ALL

WHEN(region_name = ‘Europe’) THEN — if region_name is equal to Europe

INTO TESTTABLE1 (column1,column2) — we are inserting data to Testtable1

VALUES (‘Europe’,’Europe’)

WHEN(region_name = ‘Asia’) THEN  — if region_name is equal to Asia

INTO TESTTABLE2 (column1,column2,column3) — we are inserting data to Testtable2

VALUES (‘Asia’,’Asia’,’Asia’)

ELSE –for other region_names in regions table we are inserting data into Testtable3

INTO TESTTABLE3(column1,column2,column3)

VALUES (‘Others’,’Others’,’Others’)

SELECT * FROM REGIONS;

— all of the when clauses are evaluated like a case or if  in JAVA programs, They don’t mean else if in this structure

–we can see the result here

TRUNCATE TABLE TESTTABLE1;

INSERT ALL

WHEN(region_name = ‘Europe’) THEN

INTO TESTTABLE1 (column1,column2)

VALUES (‘Europe’,’Europe’)

WHEN(region_id = 1) THEN –Europe’s regionid is 1 in Regions table so that this is executed again

INTO TESTTABLE1 (column1,column2)

VALUES (‘Europe’,’Europe’)

SELECT * FROM REGIONS;

SELECT * FROM TESTTABLE1;

COLUMN1              COLUMN2

——————– ——————–

Europe               Europe

Europe               Europe

2 rows selected

— we have seen two records as we expected

Here we can examine INSERT FIRST ;

It is really similar to INSERT ALL WHEN but by saying FIRST only the first matching WHEN is executing and the other whens are ignored

TRUNCATE TABLE TESTTABLE1;

INSERT FIRST

WHEN(region_name = ‘Europe’) THEN

INTO TESTTABLE1 (column1,column2)

VALUES (‘Europe’,’Europe’)

WHEN(region_id = 1) THEN –Europe’s regionid is 1 in Regions table so that this is executed again

INTO TESTTABLE1 (column1,column2)

VALUES (‘Europe’,’Europe’)

SELECT * FROM REGIONS;

SELECT * FROM TESTTABLE1;

COLUMN1              COLUMN2

——————– ——————–

Europe               Europe

1 rows selected

–second when is also true but by providing FIRST the second is never executed so we see only one record in Testtable1

Also you can insert into a SQL select

INSERT INTO (

 SELECT region_id,region_name from REGIONS

) VALUES (999,’new value’);

SELECT * FROM REGIONS;

REGION_ID              REGION_NAME

———————- ————————-

1                      Europe

2                      Americas

3                      Asia

4                      Middle East and Africa

999                    new value

5 rows selected

The last example is with INSERT INTO RETURNING

With this syntax for example you can get your last inserted data from INSERT statement

CREATE SEQUENCE TESTSEQUENCE INCREMENT BY 1

START WITH 501 MAXVALUE 9999999999 MINVALUE 500; — create a sequence for demonstration purpose

DECLARE

ret VARCHAR2(15) :=”;

BEGIN

INSERT INTO TESTTABLE1 (column1,column2)

VALUES (TESTSEQUENCE.nextval,’1′) RETURNING column1 INTO ret; –here we get newly inserted sequence value

dbms_output.put_line(‘inserted sequence is >>> ‘ || ret);

INSERT INTO TESTTABLE1 (column1,column2)

VALUES (TESTSEQUENCE.nextval,’1′) RETURNING column1 INTO ret; –here we get newly inserted sequence value

dbms_output.put_line(‘inserted sequence is >>> ‘ || ret);

END;

inserted sequence is >>> 501

inserted sequence is >>> 502

With this last example, we can insert into a destination table which has the same structure (I mean columns)

as source table.

DESC TESTTABLE3

Name                           Null     Type

—————————— ——– —————

COLUMN1                                 VARCHAR2(20)

COLUMN2                                 VARCHAR2(20)

COLUMN3                                 VARCHAR2(20)

3 rows selected

DESC TESTTABLE2

Name                           Null     Type

—————————— ——– —————–

COLUMN1                                 VARCHAR2(20)

COLUMN2                                 VARCHAR2(20)

COLUMN3                                 VARCHAR2(20)

3 rows selected

SELECT * FROM TESTTABLE2;

COLUMN1              COLUMN2              COLUMN3

——————– ——————– ——————–

Test2                Test2                Test2

Test2                Test2                Test2

2 rows selected

SELECT * FROM TESTTABLE3;

COLUMN1              COLUMN2              COLUMN3

——————– ——————– ——————–

0 rows selected

— after here

INSERT INTO TESTTABLE3 –TESTTABLE3 get all rows in TESTTABLE3

SELECT * FROM TESTTABLE2 –This syntax is really handy for replication purposes

SELECT * FROM TESTTABLE3;

COLUMN1              COLUMN2              COLUMN3

——————– ——————– ——————–

Test2                Test2                Test2

Test2                Test2                Test2

2 rows selected

–it works

–all the data in TESTTABLE2 is now transfered to TESTTABLE3

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s