Insert Data Into three joined tables using a stored procedure

Question

I have three tables [PublicationNotice], [PublicationNoticeClient] and [PublicationNoticeInvoice].

Table1 have one-to-one relation with table2 and table3.

I am using a stored procedure to insert data into the tables. My form consists of all attributes from table1, table2 and table3.

My problem is, when I Submit data into these three tables it inserts all data accept table2_id and table3_id in table1.

HINT: I am using ASP .Net and MSSQL

My stored procedure looks like this:

CREATE procedure [dbo].[AddUpdatePublicationNotice]
    @ID bigint = NULL,
    @Title varchar(max)= NULL,
    @NewspaperName varchar(50) =NULL,
    @Cities varchar(max)= NULL,
    @PublicationSize varchar(8) =NULL,
    @PublicationDate date =NULL,
    @PublicationType varchar(50)= NULL,
    @NewspaperPageNo smallint= NULL,
    @Colored bit= NULL,
    @CaseNature varchar(15)= NULL,
    @ImagePath varchar(max)= NULL,
    @ClientId bigint =NULL,
    @InvoiceId bigint= NULL,
    @CreatedById bigint = NULL,
    @EditedById bigint= NULL,
    @EditedDate datetime =NULL,
    --******************************************--
    @ClientName varchar(max)= NULL,
    @ClientType varchar(50)= NULL,
    @ClientContactPerson varchar(max)= NULL,
    @ClientAddress varchar(max)= NULL,
    @ClientCity varchar(50) =NULL,
    @ClientCountry varchar(50)= NULL,
    --******************************************--
    @InvoiceDate date= NULL,
    @InvoiceTotalAmount bigint= NULL,
    @InvoicePaymentRecievedDate date= NULL,
    @InvoiceChequeNo bigint= NULL,
    @InvoiceBankName varchar(50)= NULL
    --******************************************--
    AS
    Insert into PublicationNotice (
       [Title]
      ,[NewspaperName]
      ,[Cities]
      ,[PublicationSize]
      ,[PublicationDate]
      ,[PublicationType]
      ,[NewspaperPageNo]
      ,[Colored]
      ,[CaseNature]
      ,[ImagePath]
      ,[ClientId]
      ,[InvoiceId]
      ,CreatedById
      )Values(
       @Title
      ,@NewspaperName
      ,@Cities
      ,@PublicationSize
      ,@PublicationDate
      ,@PublicationType
      ,@NewspaperPageNo
      ,@Colored
      ,@CaseNature
      ,@ImagePath
      ,@ClientId
      ,@InvoiceId
      ,@CreatedById)

      insert into [dbo].[PublicationNoticeClient] ( 
       [Name]
      ,[Type]
      ,[ContactPerson]
      ,[Address]
      ,[City]
      ,[Country]
      ,[CreatedById]) 
      Values(@ClientName
      ,@ClientType
      ,@ClientContactPerson
      ,@ClientAddress
      ,@ClientCity
      ,@ClientCountry
      ,@CreatedById)

      Insert Into [dbo].[PublicationNoticeInvoice] (
       [Date]
      ,[TotalAmount]
      ,[PaymentRecievedDate]
      ,[ChequeNo]
      ,[BankName]
      ,[CreatedById])
      Values (
       @InvoiceDate
      ,@InvoiceTotalAmount
      ,@InvoicePaymentRecievedDate
      ,@InvoiceChequeNo
      ,@InvoiceBankName
      ,@CreatedById)
      GO

I know I can first insert table2 and table3 values and then select the last inserted values from table2 and table3 (that are table2_id and table3_id) and then insert them into table1

Is there any other fast way to insert data like this ???

Answer 1

You can use @@IDENTITY , SCOPE_IDENTITY, IDENT_CURRENT or OUTPUT methods to retrieve last ID. The Output is the only secure one.

You need to insert into a table variable first and then query it

create table table1(
id int identity(1,1), 
id_table2 int,
id_table3 int);

create table table2 (
id int identity(100,1),
val varchar(20));

create table table3 (
id int identity(200,1),
val varchar(20));

declare @varTable2 table (LastID int);
declare @varTable3 table (LastID int);

insert into table2 
output inserted.id into @varTable2 values ('a');

insert into table3 
output inserted.id into @varTable3 values ('a');

insert into table1 (id_table2, id_table3)  values
( (select LastID from @varTable2), 
  (select LastID from @varTable3)
  );

  select * from table1

Answer 2

--You need to declare two variables to get identity values from table 2 and table 3 As
DECLARE @table2_identity AS INT
DECLARE @table3_identity AS INT

--After insert in Table 2 set table2_identity variable as follows
SET @table2_identity = SELECT SCOPE_IDENTITY()

--After insert in Table 3 set table3_identity variable as follows
SET @table3_identity = SELECT SCOPE_IDENTITY()

--Then assign those variable values in insert query of Table 1