SQL Server stored procedure to insert in multiple tables

Question

I have 2 tables, custlogin and custinfo:

custlogin:

custid int primary key auto notnull
custusename varchar(25)
custpassword varchar(50)

custinfo:

custid foriegnkey custlogin.custid ondelete set NULL
custfirstname varchar(25)
custlastname  varchar(25)
custaddress   varchar(100)

I want to write a stored procedure which will insert into both tables

More precisely, insert into custlogin with custusername custpassword, which would return custid for use as foreign key for custinfo.

I have searched much but I didn't find any solution.

You couldn't have searched that hard - http://stackoverflow.com/questions/13318924/how-do-i-insert-into-two-tables-all-at-once-in-a-stored-procedure — Goose, Jan 07 '14 at 04:36
check in this link - http://stackoverflow.com/questions/5762942/stored-procedure-to-insert-two-tables-with-relationship — SAT, Jan 07 '14 at 04:41

score 19 · Accepted Answer · edited Jan 08 '20 at 13:57

It will be something like below. You can use SCOPE_IDENTITY() to get the last autogenerated ID withing the scope which is this stored proc in this case:

create procedure NameOfYourProcedureHere
as
begin
SET NOCOUNT ON;
SET XACT_ABORT ON;

    insert into custlogin(custusename, custpassword) 
        values ('','') -- put values here (from parameters?)

    insert into custinfo(custid, custfirstname, custlastname, custaddress)
        values (SCOPE_IDENTITY(), '', '', '')  -- put other values here (from parameters?)

SET NOCOUNT OFF;
SET XACT_ABORT OFF;
end

SQL Server stored procedure to insert in multiple tables

1 Answers1

Linked