SQL - scope_identity() for INSERT SELECT

Question

There is something similar to scope_identity() to return the ids created by giving a insert on the basis of a select? (insert of more than one line simultaneously).

OBS: scope_identity() only one of the ids.

Answer 1

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

Following is model, assuming that the table Register contains the columns ID, Name and Address, and ID has the property IDENTITY.

-- 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;

Be aware that if there is procedure Trigger associated with the table, of type INSTEAD OF INSERT, this can affect the result if it is not correctly built.