Toad World® Forums

Insert

i need one insert query in oracle but it will insert multiple row in table.

Please help me to do.

Evening “parthipanmurali”,

i need one insert query in oracle but it will insert multiple row in table.
Insert into some_table
select stuff from another_table;
That’s one way of doing it. You might need to adjust the insert statements if there are columns that are present in some_table that are not part of the select from another_table:
insert into some_table (a,b,c,d)
select d,c,b,f from another_table;
And such like.
If on the other hand, you do not have a suitable existing table, then you could try something like the following:
create table my_table (a number, b date, c varchar2(100));
with my_data as (
select 1 as my_number, sysdate as my_date, ‘Norm [TeamT]’ as my_varchar2 from dual union all
select 2, sysdate-10, ‘Hello’ from dual union all
select 3, to_date(‘07/04/1960’, ‘dd/mm/yyyy’, ‘Norm’‘s Birthday’ from dual union all

select 999, sysdate+365, ‘The end’ from dual
)
insert into my_table (a, b, c)
select my_number, my_date, my_varchar2 from my_data;
commit;
The “with” statement sets up a table, in memory, with all the data in the various select … union all and, using a single INSERT, loads it into my_table, which was created previously.
Obviously, when you come to do it (homework?) you would create the table(s) differently and use your own data.
HTH
Cheers,
Norm. [TeamT]

Good morning, Thanks for your advise .

i need to know oracle sql query optimization, please guide me to learn properly.

Hi there, I would help if you could be more specific.

Is this something running in a production environment? If so, please provide information on the number of rows, general usage, required availability, the actual SQL, anything you can.

If it is a homework assignment, that’s a totally different story.

There are several ways to optimize an insert, my favorite being bulk collect used with a forall statement. That requires PL/SQL.

For all I know, you might just want to use an append hint (a blocking activity) to slam data into a table. Insert /*+ append */ into mytable.

On May 11, 2015, at 5:27 AM, parthipanmurali bounce-parthipanmurali@toadworld.com wrote:

RE: INSERT

Reply by parthipanmurali
Good morning, Thanks for your advise .

i need to know oracle sql query optimization, please guide me to learn properly.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Oracle notifications altogether.

Toad World - Oracle Discussion Forum

Flag this post as spam/abuse.

jpdominator wrote …
I agree with jpdominator, SQL performance tuning is a world away from being able to insert multiple rows into a table in one INSERT statement.
I would advise learning basic SQL from a decent book or course, if you can, first.
Then you can consider:
Bind variables
ROWIDs
Hints
Indexes
IOTs
Bulk Inserts
Etc
And then there’s the Disk IO - placement of your data files that the table sits in, etc etc.
Then there’s the amount of CPUs and speed that you have, memory, etc etc.
Then there’s “other stuff” that hits the database server throughput - like screen savers (don’t ask, it happens!) other databases, running application servers, backups, etc etc.
Welcome to the world of databases!
Cheers,
Norm. [TeamT]

i need one insert query in oracle but it will insert multiple row in table.