How to Insert ID from one tale to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

How to Insert ID from one tale to another

Author

Topic

msettip
Starting Member

6 Posts

Posted - 2002-07-25 : 10:47:19

I am trying to create a stored procedure to add a new user. I have two tables - "Login" and "UserInfo". The "Login" has the Identity column of UserID which updates automatically when information is inserted. However when I do a multi table insert I cannot figure out how to have the foreign key "UserID" update itself in the "UserInfo" table. The "UserInfo" table has a primary key of "Owner ID".

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-25 : 10:52:28

Take a look at this:

http://www.sqlteam.com/item.asp?ItemID=8003

msettip
Starting Member

6 Posts

Posted - 2002-07-25 : 10:58:07

Thank you. It looks like I will need to create a trigger to have the foreign key update when the primary key updates. Thank you.

msettip
Starting Member

6 Posts

Posted - 2002-07-25 : 11:56:32

Did some reading on Triggers in my SQL book and online help. I am still lost. It seems like a pretty basic thing. When a Primary key is updated in one table the foreign key that relates to it gets updated also.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-25 : 12:04:44

You're not updating the values in a foreign key. You're trying to insert rows into a table that has references another table. You need to insert the parent table first, get the @@IDENTITY for the new inserted row, then include that in the remaining inserts for the dependent tables. A trigger on the parent table won't be able to do all of this successfully because it knows nothing about the data being inserted into the dependent tables.

You'll have to post the table structures for your tables and the code for the stored procedure.

msettip
Starting Member

6 Posts

Posted - 2002-07-25 : 14:01:29

Thanks:

Table: Authenticate

UserID
UserName
Password

Table: Users

OwnerID
FirstName
LastName
Email
Phone
UserID

Stored Procedure:

Alter PROCEDURE spMattysTest

@UserName nvarchar(50),
@Password nvarchar(50),
@FirstName nvarchar(50),
@LastName nvarchar(50),
@UserID int output

AS

BEGIN
INSERT INTO Authenticate (UserName, Password)

VALUES (@UserName, @Password)

SET @UserID = @@IDENTITY

INSERT INTO Users (FirstName, LastName, UserID)

VALUES (@FirstName, @LastName, @UserID)
END

Edited by - msettip on 07/25/2002 14:02:02

Edited by - msettip on 07/25/2002 14:02:20

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-25 : 14:23:04

I don't see anything wrong with this code...does it work? Does it do what you want, or do you need to do more with it?

I don't think it will matter, but try this:

ALTER PROCEDURE spMattysTest

@UserName nvarchar(50),
@Password nvarchar(50),
@FirstName nvarchar(50),
@LastName nvarchar(50)
AS
DECLARE @UserID int
BEGIN
INSERT INTO Authenticate (UserName, Password) VALUES (@UserName, @Password)
SET @UserID = @@IDENTITY
INSERT INTO Users (FirstName, LastName, UserID) VALUES (@FirstName, @LastName, @UserID)
END

Unless you need to return the @@IDENTITY value to an external process you don't need to declare it as an output variable. If it was causing problems before then this modification might fix it.

msettip
Starting Member

6 Posts

Posted - 2002-07-25 : 14:40:06

Yup. This code finally works. This was my first go at using SQL Server. So I wasn't to keen on Queries or Stored Procedures. I think this should get me going in the right direction.

Now I need to write the ColdFusion page.

Thank you very much. Don't be suprised if you see me around a bunch more.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-25 : 14:51:34

Any time, keep on coming back.

Stored procedures are god, learn them and use them. You will never get the full potential out of SQL Server without stored procedures.

msettip
Starting Member

6 Posts

Posted - 2002-08-06 : 10:48:52

It's been about a week or so now and I am getting the hang of STORED PROCEDURES. They are great. Thank you very much for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources