Click here to Skip to main content
14,332,423 members
Rate this:
Please Sign up or sign in to vote.
See more:
Kindly solve my problem for me:

I have a table named tblAddress that contain these fields:

AddressID,
Address1,
Address2,
State,
Country.


This table is related to another one named tblEmployee with the following fields:

EmpID,
AddressID,
FName,
LName

My problem is how to insert data into the two tables at once since the addressid must be inserted into the two tables, so that it will easy for me to retrieve.
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 1

INSERT tblAddress(Address1, Address2, State, Country)
  VALUES(@Add1, @Add2, @State, @Country)

DECLARE @ID INT = SCOPE_IDENTITY()

INSERT tblAddress(AddressID, FName, LName)
  VALUES(@ID, @FName, @LName)
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 2

Well, you can't just write one SQL statement to solve all of your problems.

I'm assuming that multiple employees can have the same address. So, first you have to see if the address is in the table. Then, if it is, pull out the id. If it's not, create a new ID and add the address to the table. Then, add the employee to the employee table.

At a minimum, it's two SQL statements ("SELECT AddressID WHERE ..." & "INSERT INTO tblEmployee ...")

At a max, it's three, though really, are you checking to see if the EmpID has already been added. If it has, you may need to update the other info which would make a max of 4 SQL statements ("SELECT AddressID ..." & "INSERT INTO tblAddress" & "SELECT EmpID ..." & "INSERT INTO tblEmployee")

Of course, based on how you set your addressid, you may also need to iterate through the table to find the next id.

[Well, the other answer is one SQL statement technically...]
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100