CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Patrick
Patrick

Reputation: 2577

Can I grab the inserted IDs when doing multiple inserts?

In my head this sounds improbable, but I'd like to know if I can do it: 

INSERT INTO MyTable (Name)
VALUES ('First'),
('Second'),
('Third'),
('Fourth'),
('Fifth');

SELECT INSERTED Name, ID FROM TheAboveQuery

Where ID is an auto-indexed column?

Just to clarify, I want to select ONLY the newly inserted rows.

Upvotes: 1

Views: 102

Answers (2)

Dmitry Kolchev
Dmitry Kolchev

Reputation: 2216

Starting with SQL Server 2008 you can use OUTPUT clause with INSERT statement

DECLARE @T TABLE (ID INT, Name NVARCHAR(100))

INSERT INTO MyTable (Name)
OUTPUT INSERTED.ID, INSERTED.Name INTO @T
VALUES 
   ('First'),
   ('Second'),
   ('Third'),
   ('Fourth'),
   ('Fifth');

SELECT Name, ID FROM @T;

UPDATE: if table have no triggers

INSERT INTO MyTable (Name)
OUTPUT INSERTED.ID, INSERTED.Name
VALUES 
   ('First'),
   ('Second'),
   ('Third'),
   ('Fourth'),
   ('Fifth');

Upvotes: 4

S3S
S3S

Reputation: 25122

Sure, you can use an IDENTITY property on your ID field, and create the CLUSTERED INDEX on it

ONLINE DEMO

create table MyTable (  ID int identity(1,1), 
                        [Name] varchar(64),
                        constraint [PK_MyTable] primary key clustered (ID asc) on [Primary]
                     )


--suppose this data already existed...
INSERT INTO MyTable (Name)
VALUES 
('First'),
('Second'),
('Third'),
('Fourth'),
('Fifth');


--now we insert some more... and then only return these rows
INSERT INTO MyTable (Name)
VALUES 
('Sixth'),
('Seventh')


select top (@@ROWCOUNT)
    ID, 
    Name 
from MyTable
order by ID desc

@@ROWCOUNT returns the number of rows affected by the last statement executed. You can always see this in the messages tab of SQL Server Management Studio. Thus, we are getting the number of rows inserted and combining it with TOP which limits the rows returned in a query to the specified number of rows (or percentage if you use [PERCENT]). It is important that you use ORDER BY when using TOP otherwise your results aren't guaranteed to be the same

From my previous edited answer...

If you are trying to see what values were inserted, then I assume you are inserting them a different way and this is usually handled with an OUTPUT clause, TRIGGER if you are trying to do something with these records after the insert, etc... more information would be needed.

Upvotes: 2

Related Questions