Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 6th, 2004, 03:43 PM
Authorized User
 
Join Date: Jul 2003
Location: Houston, Texas, USA.
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to nvillare
Default INSERT multiple rows to a table

Is there a way to insert multiple rows into a table in SQL without inserting one at a time like so:

INSERT INTO emp
VALUES(7698, 'Blake', 'Manager', 7839, '01-MAY-81', 2850, 30);

I want to insert multiple rows without having to re-type this row over and over again.


Thanks!

nvillare
__________________
Thanks!

N
  #2 (permalink)  
Old January 7th, 2004, 07:45 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Not unless you can formaulate the data to be inserted as a select statement:
Code:
INSERT INTO emp
(SELECT id, surname, jobtitle, manageriD... FROM tblWhatever...)
You may have to do two stages, put the data into a temporary table first then insert from that table afterwards.



--

Joe
  #3 (permalink)  
Old January 27th, 2004, 04:17 AM
Registered User
 
Join Date: Jan 2004
Location: amman, , Jordan.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi there .....
you have to use this symbol & if u want to insert more than one record into your table , the idea is so easy .. just use the sign before the column aheader name ... and oracle will ask u to enter the value of that column .... if u want to execute the command again just reexecute it .
this is an example :

insert into emp(empno,ename,job,mgr) values (&empno,'&ename','&job',&mgr);

try it .................



bond james bond
  #4 (permalink)  
Old March 9th, 2004, 01:21 AM
Authorized User
 
Join Date: Aug 2003
Location: , , Malaysia.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

as far as I know, there is now way to do a looping in SQL to insert multiple record but you can use a simple PL/SQL to do that looping

  #5 (permalink)  
Old September 13th, 2004, 09:12 PM
Registered User
 
Join Date: Sep 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try:

Insert into TableX (Column1, Column2)
Select A, B
Union All
Select C, D

Regards,
Evbo

  #6 (permalink)  
Old August 3rd, 2007, 11:48 AM
Registered User
 
Join Date: Aug 2007
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

INSERT INTO emp
select * from
(select 7698, 'Blake', 'Manager', 7839, '01-MAY-81', 2850, 30 from dual) union
(select 1235, 'Bob', 'Executive', 1235, '01-MAY-81', 2850, 30 from dual) union
(select 5465, 'Joe', 'Engineer', 5465, '01-MAY-81', 2850, 30 from dual);

  #7 (permalink)  
Old September 4th, 2007, 12:34 AM
Registered User
 
Join Date: Sep 2007
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Table Name : MENU_SEASONS
Columns : F_CODE NUMBER(6),F_NAME CHAR(40 BYTE),F_RATE NUMBER(7)

insert statement for multiple rows.

INSERT INTO MENU_SEASONS
( SELECT 1005,'COMBINATIONS OF ARABIC MEEZE',14 FROM DUAL
UNION
SELECT 1006,'ICECREAMS',3 FROM DUAL
union
select 1007,'FROZEN YOGHURT',2 from dual
union
select 1008,'KABABS,SEAFOOD AND TANDOORI',16 from dual
union
select 1009,'ITALIAN BISTRO',14 from dual
union
select 1010,'AMERICAN PIZZA',20 from dual
union
select 1011,'B-B-Q CHICKEN',22 from dual
union
select 1012,'MEXICAN CHICKEN',18 from dual
union
select 1013,'BURGERS / GRILLS',8 from dual
union
select 1014,'ROAST BEEF/BURGERS',10 from dual
union
select 1015,'CHICKEN BURGERS',6 from dual
union
select 1016,'FLAME GRILLED CHICKEN',14 from dual
);

Chandralalprabu S,
Application Engineer,
Intergraph Middle East L L C,
Oman.
       Cell:+96 892669479.
  #8 (permalink)  
Old November 3rd, 2007, 04:47 AM
Authorized User
 
Join Date: Dec 2005
Location: Lucknow, UP, India.
Posts: 28
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via Yahoo to surendraparashar
Default

Hi,

If you are uisig .net, then you can insert multiple records with the help of data adapter.

Surendra Parashar
Gurgaon, Hariyana
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
About Insert Multiple Rows of GridView to Databas divekar.vishal ASP.NET 2.0 Professional 0 April 30th, 2008 12:28 AM
INSERT Multiple rows to database at one time melkin Classic ASP Databases 19 April 11th, 2007 08:22 AM
INSERT Multiple rows to database melkin Classic ASP Professional 3 April 10th, 2007 09:03 AM
Trigger to insert rows in Remote table anirbanbasuz SQL Language 1 August 25th, 2006 06:02 AM
insert rows into the same table nik SQL Server 2000 1 October 26th, 2005 07:28 AM



All times are GMT -4. The time now is 12:18 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.