sql - Inserting into multiple tables with identity column -

insert [dbo].[transactions] ([partnerid], [transactionid], [ver], [insertedby], [inserteddatetime], [savedby], [saveddatetime])     select              [partnerid], [transactionid], [ver], [insertedby],         [inserteddatetime], [savedby], [saveddatetime]                      @partransaction;  set @transactionid = @@identity;  insert [dbo].[remittances] ([transactionid], [remittancetransactionid],[insertedby], [inserteddatetime], [savedby], [saveddatetime])     select          @transactionid, [remittancetransactionid], [insertedby],         [inserteddatetime], [savedby], [saveddatetime]                      @parremittance;  set @remittanceid = @@identity ; 

my issue here when there no record insert remittance table, @remittanceid getting identity value of transactions table.

so let's identity value inserted in transaction table 1, , there no records in remittance table, @transactionid , @remittanceid both set 1, want @transactionid value set 1 , @remittanceid null.

is there way if there no record inserted remittance table, @remittanceid value should set null.

you use @@rowcount here determine if update occurred within last statement.

  insert [dbo].[remittances]                            ([transactionid],[remittancetransactionid],[insertedby],[inserteddatetime],[savedby],[saveddatetime])                select                              @transactionid,[remittancetransactionid],[insertedby],[inserteddatetime],[savedby],[saveddatetime]                        @parremittance;                 set         @remittanceid =  @@identity ;        if(@@rowcount=0) set @remittanceid = null