Relational Database problem

Question

0.00/5 (No votes)

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 9-Mar-10 7:34am

Adesina Mark

Add a Solution

2 solutions

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...]

Posted 9-Mar-10 7:43am

William Winner

Updated 9-Mar-10 7:45am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Andy_L_J · Accepted Answer · 2010-03-09T07:43:00

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)

Posted 9-Mar-10 7:43am

Andy_L_J

Updated 10-Mar-10 7:42am

v2