Insert into two tables, both with auto increment fields

[Rizyak]

Just when I though I knew what I was doing. I ran into a roadblock.

I have two tables, organizations & users

I am building a form online for orgs to input their own information.
How do I insert into both tables when the org table needs to know what the
user_id is, but the user_id hasn't been created until the form is submitted?

Any help or request for additional info would be more than great.

Thanks!

[David Portas]

You can only insert into one table at once. Use SCOPE_IDENTITY to retrieve
the value of the last-inserted IDENTITY value.

INSERT INTO Users...

INSERT INTO Organizations (user_id, col1, col2, ...
SELECT SCOPE_IDENTITY(), 'col1 value', 'col2 value', ...

If you need to retrieve more than one IDENTITY value then use the natural
key of the table to retrieve the values after inserting. If you haven't
declared a natural key then do so.

--
David Portas
SQL Server MVP
--