Posted by Tony Rogerson on 11/06/05 18:53

Hi Mary,

The SCOPE_IDENTITY() will return the last inserted IDENTITY value for that
connection.

insert into parent (parenttext) values (p);
print scope_identity() -- gives the id from parent

insert into child (childtext) values (c);
print scope_identity() -- gives id from child

Does that help?

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Mary Walker" <123@123.com> wrote in message
news:436cefc6$1_3@mk-nntp-2.news.uk.tiscali.com...
> Hi,
>
> I'm enclosed a snippet of test code which highlights my problem. The
> Stored procedure insertValue should insert text into the parent, then
> insert other text into the child table but the 2 tables should auto
> increment in sync (i.e. so that they both end up with the same id
> numbers). I've tried taking the auto increment out of the child table but
> then I don't know how to get the right parent id into the child table.
>
> Any advice appreciated - this is my first database, so I'm just in the
> learning process really. Code follows:
>
> CREATE TABLE Parent
> (id INTEGER DEFAULT AUTOINCREMENT,
> parenttext VARCHAR(16),
> PRIMARY KEY (id))!
>
> CREATE TABLE Child
> (childID INTEGER INTEGER DEFAULT AUTOINCREMENT,
> childtext VARCHAR(16),
> FOREIGN KEY (childid) REFERENCES Parent(id),
> PRIMARY KEY (childID))!
>
> CREATE PROCEDURE insertValues(in p VARCHAR(16), in c VARCHAR(16))
> BEGIN
> insert into parent (parenttext) values (p);
> insert into child (childtext) values (c);
> END!
>
> call insertValues('from parent', 'from child')!
> select * from parent, child where parent.id = child.childid!
>

[Back to original message]