Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 26th, 2007, 05:22 PM
Registered User
 
Join Date: May 2007
Location: Arlington, VA, USA.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to TheAndruu
Default Inserting values from other tables

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
  #2 (permalink)  
Old May 26th, 2007, 05:27 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
  #3 (permalink)  
Old May 26th, 2007, 05:44 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
  #4 (permalink)  
Old May 27th, 2007, 02:21 AM
Registered User
 
Join Date: May 2007
Location: Arlington, VA, USA.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to TheAndruu
Default

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.
  #5 (permalink)  
Old May 27th, 2007, 05:00 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
  #6 (permalink)  
Old May 27th, 2007, 06:34 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
  #7 (permalink)  
Old May 27th, 2007, 09:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
je.mason@comcast.net
  #8 (permalink)  
Old May 27th, 2007, 10:47 AM
Registered User
 
Join Date: May 2007
Location: Arlington, VA, USA.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to TheAndruu
Default

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.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting Data into Multiple Tables bfoley SQL Server ASP 1 January 3rd, 2008 12:28 AM
Inserting data in multiple tables rohits PHP How-To 1 August 29th, 2006 11:32 PM
Inserting data into 2 tables chrscote Access ASP 1 August 1st, 2005 09:01 AM
inserting into multiple tables cridley SQL Server 2000 1 January 25th, 2005 12:05 PM
inserting into joined tables cjennings SQL Server 2000 3 July 23rd, 2003 06:10 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.