locked
Insert data into two tables at the same time using SQL RRS feed

  • Question

  • User-827198768 posted

    I can probably figure out how to automatically insert data into two tables using ADO.net, and make one insert directly following the first automatically.

    But can this be done using a single SQL statement? I've googled it and can't find much to help me out.

    Note: I'm using MS Access as my database.

    Thanks in advance.Smile

    Monday, March 14, 2011 11:58 AM

Answers

  • User-2010311731 posted

    No, you can not insert to two tables using one insert statement.

    Matt

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 12:21 PM
  • User-827198768 posted

    I've read about queries within queries - subqueries, is there no way these can be used?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 12:28 PM
  • User-2010311731 posted

    Only if the subquery is a select.  You can't make an insert within an insert.

     

    Matt

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 12:30 PM
  • User-1412735316 posted

    You can use trigger. Suppose you want to insert data into table1 and table2 with same query and table1 contains all the data that will be inserted into table2. Create an insert trigger on table1 which will insert data into tabel2 based on new inserted row into table1. Then with a simple query insert data into table1. Table2 will automatically be populated from table1. Be careful about one thing if there is any not null column in table2 then in table1 that column can't be null.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 12:40 PM
  • User-2010311731 posted

    You can use trigger.

    I didn't think Access uses triggers.  Is this no longer the case?

     

    Matt

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 12:42 PM
  • User-1199946673 posted

    I didn't think Access uses triggers.  Is this no longer the case?

    Correct, Access doesn't support triggers. The best way of doing this is to wrap the 2 statments inside a transaction, and either commit them both or roll the first one back if the seconde one fails:

    http://msdn.microsoft.com/en-us/library/93ehy0z8.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 1:34 PM
  • User-1412735316 posted

    Azmot thanks for the correction. I missed the last line of original post that mentioned ACCESS.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 3:31 PM

All replies

  • User-2010311731 posted

    No, you can not insert to two tables using one insert statement.

    Matt

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 12:21 PM
  • User-827198768 posted

    I've read about queries within queries - subqueries, is there no way these can be used?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 12:28 PM
  • User-2010311731 posted

    Only if the subquery is a select.  You can't make an insert within an insert.

     

    Matt

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 12:30 PM
  • User-1412735316 posted

    You can use trigger. Suppose you want to insert data into table1 and table2 with same query and table1 contains all the data that will be inserted into table2. Create an insert trigger on table1 which will insert data into tabel2 based on new inserted row into table1. Then with a simple query insert data into table1. Table2 will automatically be populated from table1. Be careful about one thing if there is any not null column in table2 then in table1 that column can't be null.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 12:40 PM
  • User-2010311731 posted

    You can use trigger.

    I didn't think Access uses triggers.  Is this no longer the case?

     

    Matt

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 12:42 PM
  • User-1199946673 posted

    I didn't think Access uses triggers.  Is this no longer the case?

    Correct, Access doesn't support triggers. The best way of doing this is to wrap the 2 statments inside a transaction, and either commit them both or roll the first one back if the seconde one fails:

    http://msdn.microsoft.com/en-us/library/93ehy0z8.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 1:34 PM
  • User-1412735316 posted

    Azmot thanks for the correction. I missed the last line of original post that mentioned ACCESS.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 14, 2011 3:31 PM