1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user3496313
  • 11
  • 2

2 Answers2

0

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

    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?)

end
anatol
  • 1,680
  • 2
  • 24
  • 47
user3496313
  • 11
  • 2
  • you answered on your own question after 1 minute since it was asked? hmm... smells like something wrong – anatol May 05 '17 at 02:19
0

If you insert 1 row to custlogin, you could use @@IDENTITY or Scope_identity() to get new inserted id.

If you insert multiple rows, then use OUTPUT to get multiple new inserted ids.

You could see an example here: http://rextester.com/TWXO81648

TriV
  • 5,118
  • 2
  • 10
  • 18
  • I would recommend to use **`SCOPE_IDENTITY()`** instead of anything else to grab the newly inserted identity value. [See this blog post for an explanation as to WHY](http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/) – marc_s May 05 '17 at 04:46
  • Thanks @marc_s, ..... – TriV May 05 '17 at 04:49