Insert into two tables at same userID at same time

Question

I want to add data in two tables in one go.

I have two tables, UserCredentials(userid,username,password,userrole) and EmployeeRecord(userid,username,firstname,lastname,desgination,nationality)

UserId is identity in both tables generated in DB, I am looking for a way that I can get userID in EmployeeRecord at which record is inserted so that I can insert login credentials at same userId in UserCredentials table. Both records need to be inserted at same time.

This is what my code looks like right now:

      create procedure enteremployeerecord ( 

            @firstName varchar(100),
            @lastName varchar(100),
            @desigNation varchar(50),
            @natioNality varchar(50),
            @userName varchar(50),
            @userPassword varchar(50),
            @userRole varchar(50)          
            )
  as
  begin

  insert into EmployeeRecord(username,firstname,lastname,designation,nationality)
  values (@userName,@firstName,@lastName,@desigNation,@natioNality)

  insert into UserCredentials(username,password,userrole)
  values (@userName,@userPassword,@userRole)

And here is LINQ SQL code in C# (Not sure though how it will change for two queries )

EmployeeRecordDBClassDataContext con = new EmployeeRecordDBClassDataContext();
con.enteremployeerecord(firstNameTextBox.Text, lastTextBox.Text, desginationTextBox.Text,nationalityTextBox.Text,userNameTextBox.Text, passwordEmployeeTextBox.Text, userRoleTextBox.Text)

I am using VS 2012 & SQL Server 2012

Not an answer to your question but you really shouldn't be storing passwords in plain text. You need to use a salted hash instead. — Sean Lange
Your tables also suffer from denormalization. You have the userrole in the credentials table. This prevents a user from having more than 1 role. Also the username should NOT be in more than 1 table. — Sean Lange

D Stanley D Stanley · Accepted Answer · 2015-02-06T14:39:42

UserId is identity in both tables generated in DB

Bad idea. Only one table needs to be responsible for the identifier. Otherwise you'd have to add a LOT of protective measures to ensure that records are always added to both tables simultaneously.

For illustration, let's assume that EmployeeRecord is responsible for generating the UserId. To insert into UserCredentials just get the generated ID and insert it:

@DECLARE @userID int  

insert into EmployeeRecord(username,firstname,lastname,designation,nationality)
values (@userName,@firstName,@lastName,@desigNation,@natioNality)

SELECT @userID = @@SCOPE_IDENTITY()

insert into UserCredentials(userid, username,password,userrole)
values (@userid, @userName,@userPassword,@userRole)

Also, with a logical 1:1 relationship between tables (which is impossible to implement in SQL since you can't insert to two tables simultaneously) I would question whether or not the tables should be merged into one. If there's no reason to keep them separate then don't separate them.

Insert into two tables at same userID at same time

1 Answers