SQL insert into with auto increment

1

Hi,
I have a simple database layout:
USER: ID auto increment
MESSAGE: ID auto increment
USER_MESSAGE: IDUser, IDMessage

I can insert my data in USER and MESSAGE separately but then because of auto increment I do not know the ID to insert proper data in USER_MESSAGE.
Is there a way to insert the proper data in USER_MESSAGE (know which auto increment is used) ?

Thanks

2

If you just insert a single row into a table with an INCREMENT column then you can get the value of the just-added-row using scope_identity() e.g.

DECLARE @MyID int

INSERT INTO MyTable ...

SELECT @MyID = scope_identity()