2

I have 2 Tables :

  1. T_Customer
  2. T_Address

In T_Customer Table have T_Address Foreign Key.

on Web Page Created form Where Customer can add there Details like First Name, Last Name, Address etc.

When Customer Submit form then Adddress details should insert in T_Address table And T_Address ID goes To T_Customer Table is it possible to Create 2 insert operations on one button click?

Is Joins is possible in this?

Serg
  • 2,346
  • 3
  • 29
  • 38
RookieCoder
  • 183
  • 1
  • 12
  • Yes, read about TRIGGER – sagi Aug 28 '17 at 13:45
  • 1
    You cant do this in a join as passing variables is not possible with inserts. You will need to insert the address and then insert the customer. You can also do this in one stored procedure using the @@IDENTITY variable. – Ross Bush Aug 28 '17 at 13:53
  • You have 2 options. 1) Write 2 ado queries wrapped in a Transaction and roll back on failure. You can easily read the inserted value back. [Execute Insert command and return inserted Id in Sql](https://stackoverflow.com/questions/18373461), [Best way to get identity of inserted row?](https://stackoverflow.com/questions/42648). 2) Use a stored proc which takes all you parameters and execute your inserts there. Either way you need to read back the Identity value of the inserted customer record (*see the links above*). – Igor Aug 28 '17 at 13:59

1 Answers1

3

A single INSERT statement can only affect one table, albeit that you might have some triggers that in turn affect other tables. However, your question was specifically about an action on one button click. This is very possible, and indeed highly advisable. Simply create yourself a single stored procedure and pass it as parameters all the fields required for both Customer and Address tables.

Then within your Stored procedure you can have two inserts, one to Customers and one to Address. If you have a referential integrity constraint, then you must insert customers before address.

The advantage of this approach, is that all commands within a stored procedure are covered by the same transaction: it all succeeds or all fails, and requires only one round trip to the database, in turn being quicker and using less resources.

Jonathan Willcock
  • 5,012
  • 3
  • 20
  • 31