Toad World® Forums

Insert multiple rows

Hi xperts,

I as fresher so my question might be basic pls do respond.

How do i write a query to insert multiple rows into the table, i.e we use IBM BPM tool in front end, user can select skill set from a List of value. on submit the value should get updated in db as follow

skill set is an array

Primary key
Employee id
Skill set
Request Id
001
123
Java
Req-001
002
123
sql
Req-001
003
123
Informatica
Req-001
I wrote a query like " inser into Table_Name (emp_id,Skill_set,Req_id) values ( employeeid,skillset,requestid)

Primary key is set as trigger in table.

but when i check the table all records are inserting into single row

Primary key
Employee id
Skill set
Request Id
001
123
Java,sql,informatica
Req-001

I’m not sure why you want to split it out like that, because the result you showed is much more efficient (1 record vs 3 records). The only thing is that
you have to extract out the values when you query that record, which can be done in many different ways using SQL or PL/SQL.

Here is just one example of how you would extract the info from that column. If you are using PL/SQL, then you could just set up a simple little loop.

select
substr(‘Java,sql,informatica’,1,instr(‘Java,sql,informatica’,’,’,1)-1)
java,

substr(‘Java,sql,informatica’,instr(‘Java,sql,informatica’,’,’,2)+1,instr(‘Java,sql,informatica’,’,’,3)-2)
sql,

substr(‘Java,sql,informatica’,instr(‘Java,sql,informatica’,’,’,-1)+1)
informatica

from dual;

As for why they are being concatenated like that, you would need to look at your BPM process, as it’s probably concatenating them together like that before
your writing them to a table, which is actually much more efficient than writing out every record. If you really want to split them out, then you would need to split them out before you write your insert.

From: Andersun [mailto:bounce-Andersun@toadworld.com]

Sent: Friday, October 31, 2014 3:23 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Insert multiple rows

Insert multiple rows

Thread created by Andersun

Hi xperts,

I as fresher so my question might be basic pls do respond.

How do i write a query to insert multiple rows into the table, i.e we use IBM BPM tool in front end, user can select skill set from a List of value. on submit the value should get updated in
db as follow

skill set is an array

Primary key

Employee id

Skill set

Request Id

001

123

Java

Req-001

002

123

sql

Req-001

003

123

Informatica

Req-001

I wrote a query like " inser into Table_Name (emp_id,Skill_set,Req_id) values ( employeeid,skillset,requestid)

Primary key is set as trigger in table.

but when i check the table all records are inserting into single row

Primary key

Employee id

Skill set

Request Id

001

123

Java,sql,informatica

Req-001

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Hi Andersun,

Better you try this: Example

Insert into Emp values(’&Ename’,Empno,’&DOB’,‘DOJ’);

Execute the Statement and insert a record…

no sooner did the Record Inserted…

Press /

then it asks the Parameters for the tab again…

Thanks,

Praveen Sai Kaligotla.