SQL – scope_identity() para INSERT SELECT

Question:

There is something similar to scope_identity() to return me the ids created to give insert based on a select ? ( insert more than one line simultaneously).

NOTE: scope_identity() returns only one of the ids .

Answer:

Filipe, you can use the OUTPUT clause to write to a table variable (or even another table) the values ​​generated during the inclusion ( INSERT statement).

The model follows, assuming that the Cadastro table contains the columns ID, Name and Address, where ID has the IDENTITY property.

-- código #1 v2
declare @tbID table (IDnovo int);

INSERT into Cadastro (Nome, Endereço) 
   OUTPUT inserted.ID into @tbID
   VALUES ('João da Silva', 'R. Paracuri, 18'),
          ('Maria da Silva', 'Av. Praia, 2965');

-- lista de novos valores
SELECT Idnovo
  from @tbID;

Note that, if there is a trigger procedure associated with the table, of type INSTEAD OF INSERT, it can affect the result if it is not correctly constructed.

Scroll to Top