Executing multiple insert into table commands in sing Oracle Command C#

Question

I want to execute all following insert into table statement in single Oracle Command is this possible?

OracleCommand cmd = new OracleCommand();
var parameter = cmd.Parameters;
string insrtPInfo = "Insert into PersonalInfo(Name, ContactNum, EmailID, Address, Gender, DOB) VALUES(:pName, :contactNum, :emailId, :address, :gender, :dob )";
string insrtEdu = "Insert into PersonalInfo(Degree, Institution, Year, CGPA_Marks) VALUES(:degree, :Instituition, :year, :marks)";
string insrtExprnce = "Insert into PersonalInfo(Organization, Organization, Desigination) VALUES(:organization, :duration, :desigination)";
string insrtSkils = "Insert into PersonalInfo(Programming_languages, Softwares, OS) VALUES(:progLang, :softwares, OS)";
string insrtProj = "Insert into PersonalInfo(FYP, Other_Projects) VALUES(:fyp, otherProj)";
// T1
parameter.Add("pName", pName);
parameter.Add("contactNum", contactNum);
parameter.Add("emailId", emailId);
parameter.Add("address", address);
parameter.Add("gender", gender);
parameter.Add("dob", dob);
// T2             
parameter.Add("degree", degree);
parameter.Add("Instituition", Instituition);
parameter.Add("year", year);
parameter.Add("marks", marks);
// T3                             
parameter.Add("organization", organization);
parameter.Add("duration", duration);
parameter.Add("desigination", desigination);
// T4           
parameter.Add("progLang", progLang);
parameter.Add("softwares", softwares);
parameter.Add("OS", OS);
// T5
parameter.Add("fyp", fyp);
parameter.Add("otherProj", otherProj);
cmd.CommandText = insrtPInfo;
cmd.ExecuteNonQuery();

How I can execute all these statements in single Oracle Command. Or any other best way to do it. I am using Visual Studio 2013 and Oracle 11g for database.

possible duplicate of Bulk Insert to Oracle using .NET

Artem Kulikov
– Artem Kulikov

2015-08-19 18:48:42 +00:00
Commented Aug 19, 2015 at 18:48 — Artem Kulikov
– Artem Kulikov, Commented Aug 19, 2015 at 18:48

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

One option is using Oracle's INSERT ALL syntax:

string sql = @"Insert all
                into PersonalInfo(Name, ContactNum, EmailID, Address, Gender, DOB) VALUES(:pName, :contactNum, :emailId, :address, :gender, :dob )
                into PersonalInfo(Degree, Institution, Year, CGPA_Marks) VALUES(:degree, :Instituition, :year, :marks)
                into PersonalInfo(Organization, Organization, Desigination) VALUES(:organization, :duration, :desigination)
                into PersonalInfo(Programming_languages, Softwares, OS) VALUES(:progLang, :softwares, :OS)
                into PersonalInfo(FYP, Other_Projects) VALUES(:fyp, :otherProj)
               select * from dual";

Documentation: multi_table_insert

ALL into_clause

Specify ALL followed by multiple insert_into_clauses to perform an unconditional multitable insert. Oracle Database executes each insert_into_clause once for each row returned by the subquery.

Another option is wrapping all your INSERT statements inside an anonymous PL/SQL block:

string sql = @"begin
                insert into PersonalInfo(Name, ContactNum, EmailID, Address, Gender, DOB) VALUES(:pName, :contactNum, :emailId, :address, :gender, :dob );
                insert into PersonalInfo(Degree, Institution, Year, CGPA_Marks) VALUES(:degree, :Instituition, :year, :marks);
                insert into PersonalInfo(Organization, Organization, Desigination) VALUES(:organization, :duration, :desigination);
                insert into PersonalInfo(Programming_languages, Softwares, OS) VALUES(:progLang, :softwares, :OS);
                insert into PersonalInfo(FYP, Other_Projects) VALUES(:fyp, :otherProj);
               end;";

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 19, 2015 at 18:50

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hanni Over a year ago

I get error " Operation is not valid due to current state of object " at cmd.executenonquery(); @sstan

sstan Over a year ago

This error has nothing to do with the statement itself. Based on the code you posted, it looks like you forgot to set the connection on the OracleCommand object. If that's not it, consider editing your post with your complete code + full error and stack trace. Or better yet, consider asking a new question, since this would no longer have anything to do with rewriting your insert statements.

Collectives™ on Stack Overflow

Executing multiple insert into table commands in sing Oracle Command C#

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related