Inserting into Table A with unique ID for B

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 into Table A with unique ID for B

Author

Topic

adcworks
Starting Member

3 Posts

Posted - 2003-06-17 : 10:57:08

Hi All,

Does SQLS have the same thing as Oracle's SELECT sequence.nextval FROM dual to ensure that I have a unique ID for use?

The situation is that I have 2 tables A and B. I need to insert into table A which has an auto id which I need to use for inserting as a foreign key to table B.

However, when I insert into A with null for the id, there is no way of getting that ID back into my ASP to use for the immediate insert into B without querying table A for the max(id)!!

I hope you can see what I am talking about.

Nazim
A custom title

1408 Posts

Posted - 2003-06-17 : 11:52:02

A) Identity field in SQl server is equivalent to Sequence in Oracle.

B) you can use a output parameter in sp and assign @@identity value. that should give you the immediate identity value assigned to the newly added record.

HTH

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Edited by - nazim on 06/17/2003 11:53:17

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-17 : 12:20:25

Here's an example:

CREATE TABLE Table1
(
T1Column1 INT IDENTITY(1, 1) NOT NULL,
T1Column2 VARCHAR(5) NOT NULL
)

CREATE TABLE Table2
(
T2Column1 CHART(3),
T2Column2 INT
)

DECLARE @HoldIdentity INT

INSERT INTO Table1 (T2Column2)
SELECT 'Tara'

SET @HoldIdentity = @@IDENTITY

INSERT INTO Table2 (T1Column1, T2Column2)
SELECT 'Aba', @@IDENTITY

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources