Is there something similar to scope_identity() to return me the ids created by giving insert based on select ? (% with more than one line at a time).
NOTE: insert returns only one of scope_identity() .
Filipe, you can use the OUTPUT clause to write to a table variable (or even another table) values that were generated during the inclusion ( INSERT statement).
This is a template, assuming that the table Cadastro contains the ID, Name and Address columns, and 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, such as INSTEAD OF INSERT, this may affect the result if it is not correctly constructed.