Inserting records into related tables using a Stored Proc

[Carl]

Hi,

I have 2 Tables. Table1 and Table2.

Table2 holds a foreign key to Table1.

How do I insert my record into Table 1 and then insert 1
or more records into Table2 whose foreign key will be the
identity of the record inserted in Table1?

Any advice or direction to SQL sites is much appreciated.

Thanks,
Carl.

[Alan]

Assuming SQL Server.

1. Declare a local variable in your proc: DECLARE @MyNewID INT
2. Insert your PK record into Table 2: INSERT INTO Table2 (...) VALUES (...)
3. Select the new PK: SET @MyNewID = SCOPE_IDENTITY()
4. Use the new PK to insert into Table 1: INSERT INTO Table1 (..., Table2FK)
VALUES (..., @MyNewID)

Regards,

Alan

"Carl" <carl@nospam.com> wrote in message
news:067101c35c8c$e601a470$a501280a@phx.gbl...

> Hi,
>
> I have 2 Tables. Table1 and Table2.
>
> Table2 holds a foreign key to Table1.
>
> How do I insert my record into Table 1 and then insert 1
> or more records into Table2 whose foreign key will be the
> identity of the record inserted in Table1?
>
> Any advice or direction to SQL sites is much appreciated.
>
> Thanks,
> Carl.