0

Assume i have a two tables with one to many relation. Shop and products.

I want to insert data to this tables in one query(ideal) or use as less as possible queries.

Straingtforward way is to insert row to shop table then get id of last record and insert rows to products table with received id. Unfortunally this is not good in my case because of i have database connection with high latency. Also i have limit access to db and cann't create stored procedures. So what is the solution?

Neir0
  • 12,849
  • 28
  • 83
  • 139
  • 1
    Nothing to do with your question, but why don't you have a many to many relationship between shops and products? – Dan Bracuk Apr 04 '13 at 17:08
  • @Dan Bracuk I cann't change DB shema. It is application with legacy code base. – Neir0 Apr 04 '13 at 17:15
  • I think the first answer to this question is what you seek: http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization – invertedSpear Apr 04 '13 at 17:41
  • @invertedSpear, that was for MySQL. For SQL Server, use SCOPE_IDENTITY() instead of LAST_INSERT_ID(). Another method is to use the OUTPUT clause of INSERT to capture the ID(s) into a temporary table or table variable (especially in case you want to insert more than one row into the shop table). Then, join to that table when doing the INSERT into Products. – GilM Apr 04 '13 at 19:14

1 Answers1

1

I am not so sure what's the request. To insert data to 2 tables, you have to use 2 insert SQL. Only one way can ensure those 2 insert work right is by using begin tran / commit / rollback and begin try / begin catch. 

begin try
    begin tran
        insert into shop...
        insert into product...

    commit
end try
begin catch
    rollback
end catch

Thus, if anything happened, it will rollback, otherwise, it will commit to the table.

Chjquest
  • 325
  • 3
  • 10