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

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
LVL 2
M3hcSSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SolutionsCSCommented:
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
BrandonGalderisiCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
M3hcSSAuthor Commented:
--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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BrandonGalderisiCommented:
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
M3hcSSAuthor Commented:
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
BrandonGalderisiCommented:
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
Scott PletcherSenior DBACommented:
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
BrandonGalderisiCommented:
To clarify... is this a snail mail or email tracking system?
0
M3hcSSAuthor Commented:
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
BrandonGalderisiCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.