The function Scope_Identity() will provide the last generated primary key value from a table insert. Is there any generally accepted way to get multiple keys from an insertion of a set (an insert resulting from a select query)?
views:
81answers:
2
+1
A:
In SQL Server 2005 onwards, you can use the OUTPUT clause to get a returned set of values. From the linked article:
The following example creates the EmployeeSales table and then inserts several rows into it using an INSERT statement with a SELECT statement to retrieve data from source tables. The EmployeeSales table contains an identity column (EmployeeID) and a computed column (ProjectedSales). Because these values are generated by the SQL Server Database Engine during the insert operation, neither of these columns can be defined in @MyTableVar.
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID int IDENTITY (1,5)NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL,
ProjectedSales AS CurrentSales * 1.10
);
GO
DECLARE @MyTableVar table(
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL
);
INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
OUTPUT INSERTED.LastName,
INSERTED.FirstName,
INSERTED.CurrentSales
INTO @MyTableVar
SELECT c.LastName, c.FirstName, sp.SalesYTD
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO
Mitch Wheat
2009-03-04 23:53:39
Perfect; didn't know about OUTPUT.
dudeNumber4
2009-03-05 14:44:41
A:
Use the row count and last identity value....
DECLARE @LastID int
DECLARE @Rows int
--your insert from a select here
SELECT @LastID=@@IDENTITY, @Rows=@@ROWCOUNT
--set of rows you want...
SELECT * FROM YourTable Where TableID>@LastID-@Rows AND TableID<=@LastID
KM
2009-03-05 14:02:58