Create an IDENTITY Column in temp tables in SQL Server

First there are different types of temporary tables that you can create in Sql Server. Such as #tempTable, ##tempTable, @tempTable. Those temporary tables can be used based on your requirement. In this example I will use #tempTable to demonstrate this topic.

Follow the below code snippet to get the job done..!

-- Create #tempTable 
CREATE TABLE #tempTable
  (
     ID        INT IDENTITY(1, 1),
     FirstName VARCHAR(10),
     LastName  VARCHAR(10),
     IsActive  BIT
  );

-- Insert values into #tempTable,
-- Do Not insert values into ID column
INSERT INTO #tempTable
            (FirstName,LastName,IsActive)
VALUES     ('Aruna','Perera',1),
           ('Damith','Ferenando',1),
           ('Kasun','Kalharaa',0)

-- Pull the data that have inserted
SELECT *
FROM   #tempTable

-- Drop temp Table
DROP TABLE #tempTable 

Make sure that your have drop the temporary table after you selecting records from that table.