1

Inserting the data in multiple tables using single query

INSERT INTO client_1.student_info, client_1.admission_details,client_1.parent_info,client_1.student_grade_mapping,client_1.parent_student_mapping(
            year1, division, id, student_id, firstname, lastname, gender, admissionno, student_id, admissiondate, tc_date, tc_issue_date,
            student_photo, dob, is_delete,student_id, parent_id, father_name, father_age, father_education, 
            father_occupation, father_ph_no, father_ph_enabled, fatheremail, 
            creation, mother_name, mother_age, mother_education, mother_occupation, 
            mother_ph_no, mother_ph_enabled, motheremail, religion, address, 
            state, city, zipcode,sg_id, student_id, grade_id,ps_id, parent_id, student_id)
    VALUES (?, ?, ?, ?, ?, ?, ?, 
            ?, ?, ?,?, ?, ?, ?, ?,?, ?, ?, ?, ?, 
            ?, ?, ?, ?, 
            ?, ?, ?, ?, ?, 
            ?, ?, ?, ?, ?, 
            ?, ?, ?,?, ?, ?,?, ?, ?);
PravinS
  • 2,640
  • 3
  • 21
  • 25
user3493933
  • 21
  • 1
  • 3

3 Answers3

0

You cannot insert data into multiple tables with a single query. You'll have to use a stored procedure or wrap two queries in a transaction.

yed
  • 322
  • 2
  • 5
0

joel-coehoorn has already mentioned here

In one statement: It's not possible

but In one transaction: It's possible

BEGIN TRANSACTION
   DECLARE @DataID int;
   INSERT INTO DataTable (Column1 ...) VALUES (....);
   INSERT INTO LinkTable (Column1 ...) VALUES (....);
COMMIT
Community
  • 1
  • 1
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
0

The answer really depends on what Database. Like the other folks said (Which many people have answered on StackOverflow) you either need a Transaction or Stored Procedure; however, if you are using Oracle 9i then you are in business (http://www.oracle-developer.net/display.php?id=209).

Happy hunting.

Jason McD
  • 567
  • 1
  • 4
  • 12
  • Transaction or SP. Sorry those are the choices. :/ – Jason McD Apr 08 '14 at 06:25
  • You'll have to break them up. Similar to what this gent is proposing. [http://postgresql.1045698.n5.nabble.com/Postgres-and-multiple-updates-in-one-statement-td1922631.html]..might be a better example on stack somewhere. Sorry wish I had a better answer. – Jason McD Apr 08 '14 at 06:39