[salguti]

hi iam relatively new to DB design...

so i wanna know if v can insert multiple rowns into a DB table with only 1 insert statement

cheers

[RBARAER]

Hello,

If you want to populate the new table with data from other existing tables, you can do that :

insert into new_table(field1, field2,...)
select (fieldA, fieldB,...)
from ...
where...
;

Note that field1 and fieldA must have the same datatype (or the fieldA datatype may be a subtype of the field1 datatype), same thing for field2 and fieldB, etc...

You can also create a new populated table by doing so :

CREATE TABLE table1 AS select ... from ... where...;

Hope it helps.

Regards,

RBARAER

[salguti]

I just want to populate newly created table with some data..i dont have anyother tables with same data..so is there any way i could populate table..instead of writing insert command for each row....

cheers
salguti

[RBARAER]

In that case, as far as I know, you will have to do one insert at a time.
In MySQL, you have

"INSERT INTO table1(field1, field2) VALUES (v1, v2),(v3,v4),...;"

, but as far as I know, that does NOT exist in Oracle, which means that you won't be able to do all inserts in one time with a SQL query.

So, if you want to do these inserts quickly, I suggest that you read raw data in a program (from a text file for example, or generated in the program), and loop on inserts... you have many ways to do that programmatically depending on the language you choose (PL/SQL, Java, C/C++, C#, ...) and if you want to do it VERY QUICKLY or not.

I may be able to help you depending on the language you choose.

Regards,

RBARAER

[shoblock]

you can use array processing in pl/sql. it allows you to populate an array and pass it once to an insert statement. This technique also exists in Pro*C.

search BULK & FORALL in pl/sql manual (example in manual):
DECLARE
TYPE num_tab IS TABLE OF NUMBER;
deptnums num_tab;
BEGIN
SELECT deptno BULK COLLECT INTO deptnums FROM DEPT;

FORALL i IN 1..deptnums.COUNT
INSERT INTO emp_by_dept
SELECT empno, deptno FROM emp WHERE deptno =
deptnums(i);

FOR i IN 1..deptnums.COUNT LOOP
-- Count how many rows were inserted for each department; that is,
-- how many employees are in each department.
dbms_output.put_line('Dept '||deptnums(i)||': inserted '||
SQL%BULK_ROWCOUNT(i)||' records');
END LOOP;

dbms_output.put_line('Total records inserted =' || SQL%ROWCOUNT);
END;
/