microsoft.public.sqlserver.programming

Aaron [SQL Server MVP]

3/27/2007 12:16:00 PM

INSERT TableA(...) SELECT ...;
INSERT TableB(..., Aid) SELECT ..., SCOPE_IDENTITY();

Some people hold it in a temporary variable first, which can be useful if
the subsequent insert is not done right away, or if the new identity value
is used for something else as well:

DECLARE @Aid INT;
INSERT TableA(...) SELECT ...;
SET @Aid = SCOPE_IDENTITY();
INSERT TableB(..., Aid) SELECT ..., @Aid;

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...




"Guy" <guy@guy.com> wrote in message
news:OqEo2dGcHHA.260@TK2MSFTNGP02.phx.gbl...
> Hi all,
>
> Newby here - with a problem!
>
> I have two tables (A and B). Table A has a unique id (Aid) and table B has
> its own unique id (Bid) but also a foreign key which is table A's unique
> id (Aid).
>
> Table A
> Aid (PK)
>
> Table B
> Bid (PK)
> Aid (FK)
>
> When inserting a new record into table A I need to take the unique id
> table A has just created (Aid) and insert this as a record into table B.
>
> I think I have to use scope_identity but really don't know where to go
> from here... any help is much appreciated!
>