Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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
Reply With Quote
  #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
================================================== =========
Reply With Quote
  #3 (permalink)  
Old May 26th, 2007, 05:44 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #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.
Reply With Quote
  #5 (permalink)  
Old May 27th, 2007, 05:00 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #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
================================================== =========
Reply With Quote
  #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
Reply With Quote
  #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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:54 AM.


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