SQL insert into related tables SQL insert into related tables sql sql

SQL insert into related tables

First, insert into TrackStrings, omitting the primary key column from the column list. This invokes its IDENTITY column which generates a value automatically.

INSERT INTO [dbo].[TrackStrings] ([String])   VALUES ('some string');

Second, insert into Tracks and specify as its TrackStringId the function SCOPE_IDENTITY(), which returns the most recent value generated by an IDENTITY column in your current scope.

INSERT INTO [dbo].[Tracks] ([TrackStringId], [Time])   VALUES (SCOPE_IDENTITY(), CURRENT_TIMESTAMP());

If you are using SQL Server 2005 or later and are inserting a lot of records in a single INSERT, you can look into OUTPUT or OUTPUT INTO options here to use the identities from the first insert in the second without haveing to "re-find" the rows to get all the IDENTITY values.

First insert into the primary table.

INSERT INTO trackstrings VALUES('myvalue')

Next get the identity. This method depends on whether you're are doing it all in 1 statement or a stored procedure or some other method. I will assume 1 statement so I'll just insert with the identity special variable.

INSERT INTO tracks VALUES( @@IDENTITY, getdate() )

Something like that should do it depending on your exact scenario. The key is the @@IDENTITY variable. It holds the last inserted identity value for the connection you are using. It is not table specific, it is simply the most recent identity inserted during the connections lifespan.