Inserting the Same Autonumber into Two Tables

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

SQL Server Development (2000)

Inserting the Same Autonumber into Two Tables

Author

Topic

StephanieJones
Starting Member

12 Posts

Posted - 2003-06-19 : 12:00:44

Can anyone help me with the following problem:

I have one ASP form inserting information into two tables (in the same database).
In table 1 I have an identity field (that automatically generates a number)
In table 2 I want the same number generated for the newly inserted record in table 1 to be inserted in a field in this table 2.

I want do this so that I can relate the two. Can someone point me on the correct path to get this done?

Thanks,
Stephanie Jones

Stephanie Jones

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-06-19 : 12:16:03

@@identity

Jonathan
{0}

StephanieJones
Starting Member

12 Posts

Posted - 2003-06-19 : 12:35:53

I am new to SQL. Can you elaborate on this answer a bit more?

Thanks,

Stephanie

Stephanie Jones

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-06-19 : 12:44:02

The @@Identity is s/t like a global variable/automatic function that holds the value of the last inserted identity column. To get its value, just do SELECT @@Identity.

Sarah Berger MCSD

StephanieJones
Starting Member

12 Posts

Posted - 2003-06-19 : 12:56:15

But in coding my form, where would I put that or does it go in the form? Or do I code the field in the second table to look up the ID just generated in the first table?

I have no clue how to verbalize what I am trying to get across other than to say, I want whatever number that is automatically generated in the first table to be inserted in the second table once the records are inserted. The information is inserted into the tables at the same time from the one form.

HELP!!!!! and Thanks!

Stephanie Jones

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-06-19 : 13:18:43

To help yourself (more) do a 'forum search' on @@IDENTITY and you'll get several working examples of the type of code to solve your problem.

you also need to look into "triggers" for the inserting of the duplicate/log/extension record into a 2nd table.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-19 : 13:28:50

you would do something like this (I don't think a trigger applies here):

1) insert your header row:

insert into HeaderTable ( ... columns to insert into ....)
values ( ... from form ....)

2) find out which ID it was assigned:

set @i = @@identity

3) now insert the related row(s):

insert into DetailTable (ID, ... other columns ....)
values (@i, .... other values .....)

Hope this gives you a clearer answer ....

- Jeff

StephanieJones
Starting Member

12 Posts

Posted - 2003-06-19 : 13:35:10

Thanks so much for your responses, I am going to see if I use your offering and try to make this work!

Thanks again,

Stephanie Jones

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-06-20 : 11:17:05

You could probably alternately do this: skip step 2, the intermediate step that assigns the @@identity to a variable, and say in step 3 "INSERT into DetailTable (ID, ... other columns ....)
SELECT @@identity, .... other values ....."

Also, if I were you, I'd wrap these two inserts into a transaction. Otherwise, if Insert 1 fails, you will be inserting Insert 2 with the wrong value from @@Identity. You use transactions by having an error handler, saying Connection.BeginTran, trying to insert, then saying Connection.CommitTran if the inserts were successful. If an error occurs, your code goes to the handler which should say Connection.RollbackTran.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources