How to Insert Data?

Question

How i can insert data into more than one table of an Access database which have a join of inner by using C#?

Answer 1

I don't know if it works in Access, but you can try:

INSERT INTO table (col1, col2, col3)
SELECT t1.col1, t1.col2, t2.col3
FROM table1 t1
INNER JOIN table2 t2 ON t1.Id = t2.Id

If you need to do this on various tables, do various inserts, JET Engine doesn't allow multiple querys on a single statement, according to here

Answer 2

I assume by join you mean a relationship with a foreign key to another table. Joins refer to queries not tables.

You can simply do multiple inserts, one to each table as required, add the record to the parent table, get the key of the new record you just added, then do the inserts to any child tables using that ID which would be the foreign key in the child table.

Answer 3

As per my experience there's no standard way but there are workarounds to this and those however depends on the structure of your tables/database.

I'm not sure Access support Triggers, SPs and Functions. I believe no so the following workarounds are not valid if it is true.

1. You could use trigger on child table but if you have a foreign key constraints that would fail since you may end up inserting values in child table which don't exists in parent. Having a trigger on parent is useless since you can't pass arguments to triggers.

2. One other workaround could be that you call a store procedure from your front end application and from that store procedure you call multiple insert statements. However from your question I deduce this is not you are willing to do since the question say "from single insert statement".

3. One other work around is some what similar to triggered approach that is to have a Function defined and you can invoke a function from an insert query. From with in the function you insert data into child table. But again if there's any integrity constraint then you can end up in the same problem as in the case of triggers. Because functions would be run first before your insert took place.

4. One other approach is; create a table which is a union of the fields from your required table (for reference lets call it root table). Then you define trigger for insert on that table. Now insert records in this root table and in the trigger insert appropriate fields in your required table. (You'll need to use Transactions in triggers for atomicity).

Suppose you have a table structure liek emp(empid,name); empContacts(contactid,empid,number); empAddress(addressId,empid,address); so you create another table which is a union of the fields from emp and empContacts name it Rootemp(empid,name,contactid,number,addressId,address);

Create a trigger for this table and from within the trigger insert records in emp,empContacts and empAddress tables with in a transaction.

However this is a workaround since you can see from front end you are calling a single insert statement however database is still calling multiple inserts.

This may not be the best option because workaround never has to be.

PS: The example of emp, empContact and empAdress may be not a very smart example but I hope it did convey the message.

Answer 4

I agree the question is vague but implies the oft asked question for the ACE/Jet engine, "If I can create an INNER JOIN between two tables, can I INSERT into both tables with one SQL statement?" usually closely followed by the qualifier, "...and if there is a FOREIGN KEY (Access = Relationship with referential integrity enforced) between the tables, can the generated INDENTITY/AutoNumber number in the referenced table be automatically added to the referencing table?"

The answer to these questions is yes: create a VIEW with an INNER JOIN (or equivalent), including the IDENTITY column and the column referencing the IDENTITY column then INSERT INTO the VIEW omitting the IDENTITY column and its referencing column.

Here's a quick example in ANSI-92 Query Mode syntax (I assume you are using OLE DB):

CREATE TABLE TableA
(
   ID IDENTITY NOT NULL UNIQUE, 
   a_col INTEGER NOT NULL
)
;

CREATE TABLE TableB
(
   ID INTEGER NOT NULL UNIQUE
      REFERENCES TableA (ID),  
   b_col INTEGER NOT NULL
)
;

CREATE VIEW TestAB
(
   a_ID, a_col, 
   b_ID, b_col
)
AS 
SELECT A1.ID, A1.a_col, 
       B1.ID, B1.b_col
  FROM TableA AS A1
       INNER JOIN TableB AS B1
          ON A1.ID = B1.ID
;

INSERT INTO TestAB (a_col, b_col) VALUES (55, 99)
;