alankwan
asked on
How to return the new inserted value for identity column ?
Any there possible to return inserted value for an indentity column? i know this is possible to using 'Output' on SQL 2005. Please advise. Thanks.
Example on SQL 2005:
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;
Example on SQL 2005:
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
don't use @@IDENTITY for sql 2000 or higher, but SCOPE_IDENTITY()
ASKER
that is work for insert .. how about update ?
UPDATE does not return any (new) identity?!
in case of update you can refer inserted.youridentityfield name
ASKER
I think this is not work for SQL 2000
the OUTPUT clause indeed does not work for sql 2000.
otherwise, you might want to re clarify what you are looking for, actually?
otherwise, you might want to re clarify what you are looking for, actually?
ASKER
Actually what i need is like this:
I will update a table based on certain condition. And also i need to insert those updated rows into a temporaly table. How can i get it done within one query? With the output clause in SQL 2005, i able to do that. But how about SQL 2000? any other way? And i dont want to use trigger to handle this as well.
I will update a table based on certain condition. And also i need to insert those updated rows into a temporaly table. How can i get it done within one query? With the output clause in SQL 2005, i able to do that. But how about SQL 2000? any other way? And i dont want to use trigger to handle this as well.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.