StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The <strong><em>only viable way</em></strong> to do this is to use <code>INT IDENTITY</code> that the SQL Server database offers. Trust me on this one - you <strong><em>don't</em></strong> want to try to do this on your own!</p> <p>Just use</p> <pre><code>CREATE TABLE dbo.YourTableOne(ID INT IDENTITY(1,1), ...other columns...) </code></pre> <p>and be done with it.</p> <p>Once you insert a row into your first table, you can retrieve the value of the identity column like this:</p> <pre><code>-- do the insert into the first table INSERT INTO dbo.YourTableOne(Col1, Col2, ...., ColN) VALUES (Val1, Val2, ...., ValN) DECLARE @NewID INT -- get the newly inserted ID for future use SELECT @NewID = SCOPE_IDENTITY() -- insert into the second table, use first table's new ID for your FK column INSERT INTO dbo.YourTableTwo (FKColumn, ......) VALUES(@NewID, ......) </code></pre> <p><strong>Update:</strong> if you need to insert multiple rows into the first table and capture multiple generated <code>ID</code> values, use the <code>OUTPUT</code> clause:</p> <pre><code>-- declare a table variable to hold the data DECLARE @InsertedData TABLE (NewID INT, ...some other columns as needed......) -- do the insert into the first table INSERT INTO dbo.YourTableOne(Col1, Col2, ...., ColN) OUTPUT Inserted.ID, Inserted.Col1, ..., Inserted.ColN INTO @InsertedData(NewID, Col1, ..., ColN) VALUES (Val1, Val2, ...., ValN) </code></pre> <p>and then go from there. You can get any values from the newly inserted rows into the temporary table variable, which will then allow you to decide which new ID values to use for which rows for your second table</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload