sqlsql-servert-sqlsql-server-2008

Create a temporary table with identity column


I am planning to create a temporary table with an extra column(Id) that will generate a number sequentially when new data is added

Id LastName FirstName
1 A B
2 C D

The column Id should maintain order for the insert logic ie, Id should increment each time an insert occurs. I also need to retrieve data from the table according to the order of Id. How do I do that?


Solution

  • When creating the table set NUM as an IDENTITY field

    CREATE TABLE #Table (
        NUM int NOT NULL IDENTITY(1,1),
        FirstName varchar(255),
        LastName varchar(255)
    );
    

    Now insert leaving NUM column (it will automatically increment

    INSERT INTO #Table (FirstName, LastName)
    VALUES
    ('A', 'B'),
    ('C', 'D');
    

    Gives:

    ID LastName FirstName
    1 A B
    2 C D