How to insert a row into another table using last inserted ID?

Question

I'm having two table on my SQL server GamerName and GamerValues, I want that, when I insert a new row on GamerName

 GamerName

    ╔════╦═════════╦
    ║ ID ║  Name   ║
    ╠════╬═════════╬
    ║  1 ║ Jeff    ║
    ║  2 ║ Geoff   ║
    ║  3 ║ Jarrod  ║
    ║  4 ║ Joel Sp ║
    ╚════╩═════════╩

So, with the last inserted ID on the table GamerName, the values should be filled on GamerValues

 GamerValues

╔════╦══════════════╦════════╗
║ ID ║  GamerID     ║ Score  ║
╠════╬══════════════╬════════╣
║  1 ║ 1            ║ 5636   ║
║  2 ║ 2            ║  148   ║
║  3 ║ 3            ║  101   ║
║  4 ║ 4            ║  959   ║
╚════╩══════════════╩════════╝

Is it possible to do it with a single query?

Answer 1

You don't need a trigger or any of the @@IDENTITY, SCOPE_IDENTITY() functions. All of them have restrictions and none of them can deal with values that aren't produced by an IDENTITY constraint. None of them can deal with multiple insertions either.

You can use the OUTPUT clause of INSERT to copy newly inserted values into another table or a table variable.

OUTPUT is also available for UPDATE and DELETE. You can retrieve the new/modified columns with the inserted. and deleted. prefixes

For these tables :

create table #GamerName 
(
    ID int IDENTITY primary key, 
    Name nvarchar(20) not null
);
create table #GamerValues(
    ID int IDENTITY primary key,
    GamerID int not null,
    Score int not null
);

You can insert new records in #GamerName and copy the generated ID to #GamerValues with :

INSERT INTO #GamerName (Name)
OUTPUT inserted.ID,0 into #GamerValues(GamerID,Score)
VALUES 
('Jeff'),
('Geoff'),
('Jarrod'),

New values appear in the inserted virtual table. OUTPUT is also availa

A new line will be created for each of the gamers in GamerValues. Let's modify the default score with :

UPDATE #GamerValues 
SET Score=100

The table will look like :

ID  GamerID Score
1   1       100
2   2       100
3   3       100

Adding another gamer with

insert into #GamerName (Name)
output inserted.ID,0 into #GamerValues(GamerID,Score)
Values 
('Joel sp')

Will result in a new line with a Score of 0

ID  GamerID Score
1   1       100
2   2       100
3   3       100
4   4       0