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

Question

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

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)

3

Next, talk with 2 tables (Group1 and Group2), so 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 into the Group2 table the Id of the Group1 table that was just generated in that Save. I saw several things like that using the OUTPUT but I do not know what to do after recovering the value like that. 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>)

That is, I know how to retrieve the value but I do not know how to assign it to the second Insert clause. It would look like this in the table

Group1: Group1Id, name, age, phone
Group2: name, GroupIdIgualAboutLimitLine

It's a simple question, but I do not think that's the "end" to solve the problem, thanks to everyone.

sql sql-server c#

asked by anonymous 15.03.2016 / 15:32

3 answers

How can I get the value inside this td Product displayed is the smallest within the group (Criteria)

score 2 · Answer 1

Rather than using @@IDENTITY is to use SCOPE_IDENTITY() , which returns the last value marked in a column type IDENTITY made in the current scope , that is, that was actually inserted by the code that you are running. This is important because if tomorrow someone puts a AFTER INSERT TRIGGER into the Grupo1 table that inserts into a log table that has a column type IDENTITY , this will do so that the @@IDENTITY returns the column value of the log table , not Grupo1 .

Then the code would look like this:

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

score 0 · Answer 2

Hello, Junior

There are some ways I would do it. See if some of these help you:

Use @@ IDENTITY : @@ IDENTITY returns the last ID entered in the database, regardless 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: link

Use a query to return the last record entered in the Group1 table. This query is very simple, and can look like this:

SELECT MAX (Group1ID) FROM Group1

With these two options, you will have the Group ID1 to insert into Group2 INSERT.

I hope I have helped,

Vinicius

score 0 · Answer 3

You can do this as follows

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)