?
Solved

How do I insert data into two tables using a stored proc where there is a foreign key relationship?

Posted on 2008-01-30
10
Medium Priority
?
730 Views
Last Modified: 2011-04-14
1st table is a mail item.
2nd table is a name table.

A mail item such as a letter is mailed TO and FROM someone. Therefore that mail item has a FK_to and a FK_from name.

On the below code I get "Subqueries are not allowed in this context. Only scalar expressions are allowed." at (SELECT MAX(Name_ID) FROM tbl_Name + 1)

Mail item table...
SET xact_abort ON
BEGIN TRANSACTION
INSERT INTO tbl_MailItem
(
      MailItem_TrackingNumber,
      FK_tbl_Name_PersonFrom,
      FK_tbl_Name_PersonTo,
)
VALUES
(
      @MailItem_TrackingNumber,
      (SELECT MAX(Name_ID) FROM tbl_Name + 1),
      (SELECT MAX(Name_ID) FROM tbl_Name + 2),
)

--For the person from.
INSERT INTO tbl_Name
(
      Name_First_Name,
      Name_Last_Name,
      Name_Middle_Name
)
VALUES
(
      @FROM_Name_First_Name,
      @FROM_Name_Last_Name,
      @FROM_Name_Middle_Name
)
--For the person to.
INSERT INTO tbl_Name
(
      Name_First_Name,
      Name_Last_Name,
      Name_Middle_Name
)
VALUES
(
      @TO_Name_First_Name,
      @TO_Name_Last_Name,
      @TO_Name_Middle_Name
)
COMMIT TRANSACTION
0
Comment
Question by:M3hcSS
10 Comments
 
LVL 4

Assisted Solution

by:SolutionsCS
SolutionsCS earned 500 total points
ID: 20779449
Replace
      (SELECT MAX(Name_ID) FROM tbl_Name + 1),
      (SELECT MAX(Name_ID) FROM tbl_Name + 2),
With
      (SELECT MAX(Name_ID) FROM tbl_Name) + 1,
      (SELECT MAX(Name_ID) FROM tbl_Name) + 2,
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1000 total points
ID: 20779845
You can't have subqueries in a values clause.  You would have to use a SELECT instead of a VALUES.

But I have to question why FK_tbl_Name_PersonFrom and FK_tbl_Name_PersonTo are going to be the next two values.  Are they identity fields in the  TBL_Name table?  What if they already exist?

Try this:
SET xact_abort ON
BEGIN TRANSACTION
declare @mail_To_Id   int,
        @Mail_From_Id int
 
select @Mail_To_Id = Name_ID from TBLName
where   Name_First_Name = @to_Name_First_Name and 
      Name_Last_Name = @to_Name_Last_Name and 
      Name_Middle_Name = @to_name_middle_Name
if @Mail_to_Id is null
begin
INSERT INTO tbl_Name
(Name_First_Name,Name_Last_Name,Name_Middle_Name)
VALUES (@TO_Name_First_Name,@TO_Name_Last_Name,@TO_Name_Middle_Name)
select @mail_To_Id = scope_identity()
end
 
select @Mail_From_Id = Name_ID from TBLName
where   Name_First_Name = @from_Name_First_Name and 
      Name_Last_Name = @from_Name_Last_Name and 
      Name_Middle_Name = @from_name_middle_Name
if @Mail_from_Id is null
begin
INSERT INTO tbl_Name
(Name_First_Name,Name_Last_Name,Name_Middle_Name)
VALUES (@from_Name_First_Name,@from_Name_Last_Name,@from_Name_Middle_Name)
select @mail_from_Id = scope_identity()
end
 
 
 
INSERT INTO tbl_MailItem
(MailItem_TrackingNumber,FK_tbl_Name_PersonFrom,FK_tbl_Name_PersonTo)
VALUES
(@MailItem_TrackingNumber,@mail_From_Id,@mail_To_Id )
 
COMMIT TRANSACTION

Open in new window

0
 
LVL 2

Author Comment

by:M3hcSS
ID: 20780413
--But I have to question why FK_tbl_Name_PersonFrom and FK_tbl_Name_PersonTo are going to be the next two values.  Are they identity fields in the  TBL_Name table?  What if they already exist?--

Yes...the tbl_Name has its PK as a foreign key in tbl_MailItem. Originally tbl_Name was suppose to have unique names but...how does one put all the names in the world in the database? One could add an interface for the users to enter names as they come in but still the drop down list would eventually be populated by thousands of names.

The database only has to answer one question, "who sent who what mail item when?" I cannot know if the application will be expanded in the future.

Both the above answer my question so I will close it later today...but if anyone has a better alternate system for storing mail items with names and addresses than what I have now I would love to hear it.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20780550
Well if you are only recording the first, middle and last names, they are not unique.  So what value do you have in recording "Jom Bob Smith" 1,000,000 times potentially.  

But... if you wish to do that, I still say the way to do it is to insert into the tbl_names first and use the identity values to populate


Basically... Why are you denormalizing the names into the tbl_names column if you have no intent on re-using it?
0
 
LVL 2

Author Comment

by:M3hcSS
ID: 20780902
I know, right now, it's not a suitable database design but at the time I thought the names would be unique and populated with good data including unique SSN. But the reality is that didn't happen; it ended up being an information depository whereby people could go back in time to see what mail items have been sent. It's a legal requirement and the legacy system upon which this program is based on doesn't get used often.

How would I go about creating a system that would have unique names and is easy for the users to enter information for each mail item? How to have the user names from a picklist of many without becoming unweildy, both to select and to enter new names?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20781002
The problem is that a name itself is not unique.  But email address is.  I think that you should add the EmailAddress to the tbl_Names table which WILL uniquely identify a user.  Create a unique constraint on that field as well.  Granted, you will only be able to have an nVARCHAR(450) email field in order to be able to create a unique index.  Alternatively, you can create an email field and store the MD5 hash of the email address in a separate column with the unique index.

There will be a lot of cleanup to do.  Especially if you don't have the email address available.  But it IS an email system.  And that is what identifies a person.  You could end up with more than one Jim Smith record.  One per email address actually.  

Or.  Normalize it further.  Have the primary email address in the tbl_Names table and a child tables with all other email_Addresses.  This will not allow you to track the email address used in the the transaction in the tblMailItem table, rather only the person associated with the email address.

If this is SNAIL MAIL we are talking about, you may need to choose some other type of identifier like address.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 20781392
Yep, you definitely have a serious design issue there.  Obviously name by itself will never be unique.

You have several possibilities for a "key" to distinguish these values, but any of them could change:
email address (may not be applicable to everyone);
phone number (may not be applicable to everyone);
birthdate (you may not know / he/she may not be willing to provide);
ssn (you may not know / he/she may not be willing to provide / not applicable to everyone);
zip code / postal code (if not in u.s.) (may not be unique enough).

Personally, I would try something like zip code, since:
1) that info is not personal, most people will be happy to provide it
2) unless you have a very large customer base, you probably won't have that many overlaps
3) if you do get overlaps, you can ask those people (only) to provide some additional identifying info.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20781584
To clarify... is this a snail mail or email tracking system?
0
 
LVL 2

Author Comment

by:M3hcSS
ID: 20786579
It's a snail mail system used as a legal requirement to track letters and packages to and from various clients from within the hospital. It's a legal requirement to track said items although, in practice, information is entered but not used except to track postage costs.

Actually, I just had an idea; a hybrid system in which I'll create and populate another table called tbl_client which contains all the unique data of our clients. Another table holds the external names and external addresses which would function as a dump. It would more a more useful solution that mixing up names and addresses.

This way, I think, a select query could be made for each name, and postal items could be attached to each client.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20786676
That sounds like a good solution.  But your original question was how to handle the sub-select in the values clause.  Which can't be done.

It could have been accomplished with changing the values to a select, or insert into tbl_names first as I did.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question