insert data in multiple tables

Question

hi i have a problem to insert data in multiple tables. i have define primary key & reference key in tables now i want to insert data in both tables in single query.......how can i do this...........???????

Answer 1

Does your language support the INSERT ALL construct? If so, that is the best way to do this. In fact it's the only way. I posted an example of this construct in another SO thread (that example syntax comes from Oracle SQL).

The other option is to build a transactional stored procedure which inserts a record into the primary key table followed by a record into the referencing table.

Answer 2

This is what transactions are meant for. Standard SQL does not permit a single statement inserting into multiple tables at once. The correct way to do it is:

-- begin transaction
insert into table 1 ...
insert into table 2 ...
commit

Answer 3

And 1 of your choice to do that is use ORM (like Hibernate, NHibernate) the you make your object and set other relation to it and finally just save the main object , like:

A a;
B b;
C c;
a.set(b);
a.set(c);
DAO.saveOrUpdate(a);

you must notice your DAO.saveOrUpdate(a); line of code just work with hibernate but it insert data into 3 table A, B, C.

Answer 4

Your question isn't exactly clear on what the particular problem is. I can see three possibilities:
1. You want to insert into two tables wiht a single INSERT statement
2. You want to do two inserts, but without anything else being able to 'get in the middle'
3. You want to insert into one table, then get the primary key to insert into the second table

The answer to 1. is simple:

You can't.

The answer to 2. is simple too:

BEGIN TRANSACTION
   INSERT INTO <table1> (a,b,c) VALUES (1,2,3)
   INSERT INTO <table2> (a,b,c) VALUES (1,2,3)
COMMIT TRANSACTION

The answer to 3. is has several possibilities. Each depending on exactly what you want to do. Most likely you want to use SCOPE_IDENTITY() but you may also want to look up @@identity and IDENT_CURRENT() to understand the various different options and complexities.

BEGIN TRANSACTION

   INSERT INTO <dimension_table> (name)
      VALUES ('my new item')

   INSERT INTO <fact_table> (item_id, iteam_value)
      VALUES (SCOPE_IDENTITY(), 1)

COMMIT TRANSACTION