BuyerAccounts and SellerAccounts Tables that both reference Accounts Table. Create Account INSERT

I am designing tables to store accounts of website users. There will be two types of accounts for the website, BuyerAccounts and SellerAccounts. My approach is to have a master Accounts table that will store information that is common to both types of accounts and then to have a table each for BuyerAccounts and SellerAccounts.

This is the basic setup..

create table Accounts 
(
UserID_PK int identity not null primary key,
Email varchar(50) not null unique,
UserPassword varchar(50) not null,
Salt varchar(50) not null,
AccountType tinyint not null,
);

create table SellerAccounts
(
SellerID_PK int not null primary key foreign key references Accounts(UserID_PK),
SellerColumn int
);

create table BuyerAccounts
(
BuyerID_PK int not null primary key foreign key references Accounts(UserID_PK),
DeliveryAddress string,
BuyerColumn string 
);

Each of SellerAccounts and BuyerAccounts has a Primary Key that is also a Foreign Key referencing the Primary Key of the Accounts Table.

This all seems okay so far to me but I'm a beginner so please say if I have done something wrong or bad so far.

When an account is created I want to create a record in Accounts and also a record in either BuyerAccounts or SellerAccounts.

How do I do this? I'm going to make it a stored procedure but my primary concern here开发者_JS百科 is how to make an INSERT into two tables that are linked. Say a BuyerAccount is being created. Does account creation have to be broken into two INSERTS? First into Accounts, so that the UserID_PK for the account that will also be the BuyerID_PK of BuyerAccounts is known and then the rest of the account info can be inserted into BuyerAccounts? Is this how is should work?

How do I write an INSERT that returns UserID_PK so that I have it for the second insert? Or even better can SQL Server do something clever and just work out the Values of UserID_PK and BuyerID_PK for me and do everything with just one insert?

Thank you for your help!

use scope_identity()

example

declare @id int

insert Accounts values('a@b.com','pass','bla',1)
select @id = SCOPE_IDENTITY()

insert SellerAccounts values(@id,1)

select * from SellerAccounts