Inserting Multiple Rows using single SQL statement !!!

I was surprised when i came to know that there is single SQL statement available to insert more than one row at a time. So i decided to write a post on this , inorder to share it with others.

How we generally do :

INSERT INTO TableName(Id,Name) VALUES (1,'Name1')
INSERT INTO TableName(Id,Name) VALUES (2,'Name2')

The other way is
INSERT INTO TableName(Id,Name) SELECT 1,'Name1' UNION ALL SELECT 2,'Name2' UNION ALL SELECT 3,'Name3'

Cool!!!! Isnt It?????

5 Comments »

RSS feed for comments on this post. TrackBack URI

Cool nive… thanks for ur nice info..awaiting for ur future posts..

Comment by priya — February 25, 2008 #

Reply
To insert multiple records in a table while selecting from another table :

INSERT INTO TableName1(Id, Name, Constant) SELECT id, name ,’constant value for all records’ from TableName2;

Comment by Kamal — July 8, 2008 #

Reply
Nice discovery!!

Can you let me know whether this can be used on oracle 9i/10g as well?

I tried running the same on 9i but it doesn’t recognise the syntax.

Thanks,
Shivam

Comment by Shivam — July 11, 2008 #

Reply
Hey Shivam, In case u have not yet found an answer to ur problem, it will work this way –

CREATE TABLE testTable
( id INT NOT NULL,CITY VARCHAR(50))

INSERT ALL
INTO testTable (id,CITY) VALUES (1,’NEW york’)
INTO testTable (id,CITY) VALUES (2,’London’)
INTO testTable (id,CITY) VALUES (3,’Paris’);
SELECT * FROM dual;

, worked for me!!!

Comment by Jyoti R — August 28, 2008 #

Reply
Hi! I was surfing and found your blog post… nice! I love your blog. Cheers! Sandra. R.

Comment by sandrar — September 11, 2009 #

Reply