SQL Server : Capturing multiple IDENTITY values after inserting into SQL Server table

Usually we will have situation where we need to capture IDENTITY values after inserting into table, normally this can be achieved by using SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT() functions.

IDENT_CURRENT: returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY: returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY : returns the last identity value generated for any table in the current session and the current scope.

The above functions are used to retrieve IDENTITY value for single record inserts.J

But, do you think these functions will satisfy all our requirements? My answer is NO, since they will not support for multiple inserts.

Nothing to worry, we have OUTPUT clause to capture the new IDENTITY values for a batch of inserted records which is explained below:

First, let us create sample table with few records.

CREATE TABLE [DBO].[Employee]
(
[EmployeeID] INT IDENTITY(1,1),
[EmployeeName] VARCHAR(100)
)
GO

INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Vamsi Priya');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Praveen');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Sushma');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Radhika');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Harini');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Chaitanya');

The same insertion can be done in SQL Server 2008 as below :

INSERT INTO [DBO].[Employee] ([EmployeeName])
VALUES
('Vamsi Priya'),('Praveen'),('Sushma'),('Radhika'),('Harini'),('Chaitanya')
GO

After inserting the records, let us execute and check what is the last [EmployeeID] using the below query.

SELECT @@IDENTITY
GO

The output would be 6. Still we are not satisfied with the result as we got only last inserted employee id, but not all the IDs which were inserted in the transaction.

This can be achieved using the below query :

Before this let us remove all the records from [Employee] table.

TRUNCATE TABLE [DBO].[Employee]
GO

As we know, TRUNCATE TABLE removes all rows from a table, but the table structure and columns remains. If the table contains an identity column, the counter for that column is reset to the seed value defined for the column.

DECLARE @EMPTABLE TABLE ([EmployeeID] INT,[EmployeeName] VARCHAR(100));

INSERT INTO [DBO].[Employee]
(
[EmployeeName]
)
OUTPUT INSERTED.[EmployeeID],INSERTED.[EmployeeName]
INTO @EMPTABLE ([EmployeeID],[EmployeeName])
SELECT 'Vamsi Priya' UNION ALL
SELECT 'Praveen'     UNION ALL
SELECT 'Sushma'      UNION ALL
SELECT 'Radhika'     UNION ALL
SELECT 'Harini'      UNION ALL
SELECT 'Chaitanya'
GO

Now, let us see the output :

SELECT [EmployeeID],[EmployeeName] FROM @EMPTABLE
GO

From the above example, you could understand how we can capture the multiple identity values. J

Note :

1. I have used TABLE variable to capture the records, even TEMP TABLE also be used in place of TABLE variables.
2. You can use this statement inside your stored procedure and also the result can be used for any other calculations within SP.
3. This feature is available in SQL Server 2005 and above versions.

SQL Server : Using CASE expression in an ORDER BY clause

Many times, I used to hear the question from my folks that “How can I use CASE in an ORDER BY clause?”

Let me show you, how CASE expression can be used in an ORDER BY clause to determine the sort order of the rows based on a given column value.

Example 1 :

Let us assume that we need to sort [CityName] based on @ORDER variable when we set to 1, the result set should sort by [CityName] in ascending order and @ORDER set to 2 should be in descending order.

Normal result without order by clause would be :

SELECT [CityID],[CityName] FROM [DBO].[City]

I have modified the above statement slightly to make ORDER BY based on the condition which is shown below:

DECLARE @ORDER TINYINT
SET @ORDER = 1
SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ORDER = 1 THEN [CityName] END ASC,
CASE WHEN @ORDER = 2 THEN [CityName] END DESC

Let us execute the above statement and see the output.

From the above result set, we could identify that the records were sorted by [CityName] in ascending order as @ORDER value is 1.

Now, let us change @ORDER value to 2 (descending order) and see the result.

DECLARE @ORDER TINYINT
SET @ORDER = 2
SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ORDER = 1 THEN [CityName] END ASC
CASE WHEN @ORDER = 2 THEN [CityName] END DESC
GO

As expected, the result set has been sorted by [CityName] in descending order.

Example 2 :

The same CASE expression can also be used to sort columns based on conditions. See the below example.

DECLARE @ColumnOrder VARCHAR
SET @ColumnOrder = 'CityName'

SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ColumnOrder = 'CityName' THEN[CityName] END ASC,
CASE WHEN @ColumnOrder = 'CityID' THEN [CityID]END ASC
GO

The same statement can be used in Stored Procedure as given below :

CREATE PROCEDURE [DBO].[STP_City]
(
@ColumnOrder VARCHAR(10)
)
AS
SELECT [CityID],[CityName]
FROM [DBO].[City]
ORDER BY
CASE WHEN @ColumnOrder = 'CityName' THEN[CityName] END ASC,
CASE WHEN @ColumnOrder = 'CityID' THEN [CityID]END ASC
GO

EXECUTE [DBO].[STP_City] 'CityName'
GO

Note : For this example, I have used only variables, even column names also be used in place of variables.