inserting into 2 tables in 1 SP

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

SQL Server 2000 Forums

Transact-SQL (2000)

inserting into 2 tables in 1 SP

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-08-29 : 01:02:26

I have an application form that the user fills out. Because of the database design it splits into two separate tables. What I need to do is INSERT into the first table and retreive the IDENTITY COLUMN VALUE and then INSERT that into the second table. Is this possible in 1 SP? Here's an short example of what I am trying to do

In the 2nd transaction USERID = IDENTITY COLUMN of the first INSERT

CREATE PROCEDURE [newUser]
(
@NameOnline [varchar](15),
@NameFirst [varchar](20) = NULL,
@Question1 [varchar](200) = NULL
)
AS
SET NOCOUNT ON

INSERT INTO [tblUsers]

( [NameOnline],
[NameFirst]
)

VALUES
(
@NameOnline,
@NameFirst
)

INSERT INTO [Questions]

([UserID],
[Question1]
)

VALUES

(@userID, @question2)

GO

Thank you for reading this

Mike123

j9988
Starting Member

2 Posts

Posted - 2002-08-29 : 01:31:54

YOU'D BETTER USE TRIGGER

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-08-29 : 01:40:21

I have never used one before, is it not possible to do it another way??

Thanks

samrat
Yak Posting Veteran

94 Posts

Posted - 2002-08-29 : 02:19:09

I think it is certainly achiveable using 1 stored proc.

2 Things i would do in your stored proc.

First: Open up a transaction to maintain the data integrity and
Second: As soon as u do an insert in table one you can use exec a system stored procedure that would give you the last identity seed that it is generated and take that value to do an insert in other table

Regards,

Samrat

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-08-29 : 02:19:40

Mike,

@@IDENTITY can be used to get the newly created identity in first table.
After first insert use this...

select @userid= @@IDENTITY

Ramesh

samrat
Yak Posting Veteran

94 Posts

Posted - 2002-08-29 : 02:21:36

Oops 4got to put the function that you would use to retrieve the last generated identity Value..

The function is Ident_Current('Table Name')
(Use BOL for more info)

Regards,

Samrat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources