Return the Id of table 1 and insert in table 2 in the same code

Question

Talk guys, next, to with 2 tables (Group1 and Group2), there I have a form that has several fields that will be inserted at the same time in these 2 tables. But I need to insert in the table Group2 the Id of the table Group1 that was just generated in this Save. I saw several things like this using OUTPUT but I don’t know what to do after recovering the value like this. What I need would be more or less this

insert into Grupo1
values (nome, idade, telefone)
output inserted.Grupo1Id // aqui é o valor que quero
insert into grupo2
values (<inserted.Grupo1Id>)

I mean, I know how to recover the value but I don’t know how to assign it to the clause of the second Insert. That’s how it would look in the table

• Group1: Group1id, name, age, telephone
• Group 2: name, Group

It is a very simple doubt but I do not find this "final part" to solve the problem, thank you all.

Answer 1

Better than using @@IDENTITY is to use SCOPE_IDENTITY(), which returns the last value checked in a type column IDENTITY done in the current scope, that is, it was effectively inserted by the code you are running. This is important because if tomorrow someone puts a AFTER INSERT TRIGGER on the table Grupo1 inserted into a table of log that has a column like IDENTITY, This will make the @@IDENTITY return the value of the log table, not of Grupo1.

Then the code would look like this:

insert into Grupo1
values (nome, idade, telefone);
insert into Grupo2
values (SCOPE_IDENTITY());

Answer 2

Hello, Junior

there are a few ways I would do it. See if any of these help you:

1. Use the @@IDENTITY: @IDENTITY returns the last ID inserted in the database, independent of the table. If you are sure that the last record entered was from the Group1 table, then the value of @@IDENTITY is Group1id. Reference: https://msdn.microsoft.com/pt-br/library/ms187342(v=sql.120). aspx

2. Use a query to return the last record inserted in the table Group1. This query is very simple, and can be so:

   SELECT MAX(Group1id) FROM Group1

With these two options, you will have the Group ID 1 to be inserted in the Group INSERT 2.

I hope I’ve helped,

Vinicius

Answer 3

You can do it this way

declare @Grupo1 table
(
    Grupo1Id int  IDENTITY(1,1) NOT NULL,
    nome varchar(100),
    idade int, 
    telefone varchar(15)
)

declare @Grupo2 table
(
    Grupo2Id int  IDENTITY(1,1) NOT NULL,
    Grupo1Id int
)

declare @Grupo1Id int 
insert into @Grupo1 values ('nome', 2, '222222')

set @Grupo1Id = @@IDENTITY -- aqui é o valor que quero

insert into @Grupo2
values (@Grupo1Id)