Inserting values from other tables

TheAndruu · May 26th, 2007, 05:22 PM

I'm trying to write a procedure that updates two tables when a new person is added to the database -- Account and Contacts.

My problem is when I'm trying to copy the user's ID from the Account table into the Contacts table.

An idea of what the tables look like:

Code:

[u]Account</u>
userID     password

[u]Contacts</u>
userID     emailAddress

This is going into a stored procedure, which accepts the password and email address as arguments.

Since the userID is being automatically generated, I have to pull it from the Account table and insert it into the Contacts table, and then also update the emailAddress (but I don't know what the userID is).

Here is what I basically want to do:

Code:

INSERT INTO [Account] (password) VALUES (@password)
-- Account now has the password and the automatically generated ID

INSERT INTO [Contacts] (userID, emailAddress) VALUES (??ID???, @emailAddress)
-- Contacts needs to have the userID inputted

Any help would be much appreciated. Thanks in advance.

-Andrew

dparsons · May 26th, 2007, 05:27 PM

Why not do this: place a trigger on the table and write something like this:

CREATE Trigger trigID on [tableName]
AFTER INSERT as
INSERT INTO [Contacts](userID, emailAddress)
(SELECT userID, emailAddress from Inserted)

hth

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Imar · May 26th, 2007, 05:44 PM

Or use SCOPE_IDENTITY as it's a bit more visible (and thus easier to see and modify later) than a trigger:

INSERT INTO [Contacts] (userID, emailAddress) VALUES (SCOPE_IDENTITY(), @emailAddress)

SCOPE_IDENTITY returns the most recently created ID in the current scope which is the newly inserted ID of the Account table.

Look in Books Online for more info about SCOPE_IDENTITY.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

TheAndruu · May 27th, 2007, 02:21 AM

Thanks for the quick replies!

Both of your suggestions worked, and solved what I had asked for. However, b/c of the other operations I was executing in the stored procedure, I settled with a different solution.

I simply retrieved the userID from the Account table after the record was inserted and stored it to a variable, then used that variable in the code later, as follows:

Code:

DECLARE @userID INT
SET @userID = (SELECT userID FROM UserAccount WHERE email = @email)

INSERT INTO UserContact (userID, street, city, state, zip, phone)
Values (@userID, @street, @city, @state, @zip, @phone)

One question I did have, however, was whether or not the SCOPE_IDENTITY() function was thread-safe. I wonder if you had multiple threads updating a database, creating multiple users at once, whether or not they'd get mixed up at some point if I were to go that route. The solution did work, though, as you said.

Once again, thanks both for your help, I appreciate it.

Imar · May 27th, 2007, 05:00 AM

Did you read the Books Online about SCOPE_IDENTITY as I suggested? It's all in there:

Quote:

quote:SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

It returns the ID in the current session and scope, so yes, it's thread safe.

Your solution may not be safe, though. Theoretically, two users could insert an account with an identical e-mail address and could end up with each other's ID....

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

dparsons · May 27th, 2007, 06:34 AM

Quote:

quote:Originally posted by Imar

Your solution may not be safe, though. Theoretically, two users could insert an account with an identical e-mail address and could end up with each other's ID....

This is quite right but you can circumvent this issue by doing:

SELECT count(email) from [table] where email = @email

If you return 0 the email does not exist in the database, if it does you should probably exit the procedure as an email address should be unique to each user.

hth

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Jeff Mason · May 27th, 2007, 09:47 AM

Quote:

quote:
This is quite right but you can circumvent this issue by doing:

SELECT count(email) from [table] where email = @email

If you return 0 the email does not exist in the database, if it does you should probably exit the procedure as an email address should be unique to each user.

hth

Just be sure this SELECT is executed within the same transaction that bounds the UPDATE, since without it the concurrency issues that Imar points out can still exist.

Also an EXISTS test is usually more efficient than counting:

IF EXISTS (SELECT * FROM yourtable WHERE email = @email)
RETURN <someerrorvalue>

is marginally better, IMO.

Jeff Mason
Custom Apps, Inc.
[email protected]

TheAndruu · May 27th, 2007, 10:47 AM

You're right about the potential for retrieving multiple IDs if the users had the same email address, but one of the constraints I have is that all users must have unique email addresses, so it works for me. Sorry I didn't mention that.

Maybe I'll still switch over to SCOPE_IDENTITY() though, since it's probably a bit more efficient than doing another SELECT statement.

Thanks so much for all your help.