How to insert Auto-Increment using SELECT INTO Statement? SQL SERVER

Question

This my table1:

Name        Description  
john        student       
dom         teacher

I need to use SELECT * INTO to transfer it to another table (table2) but I want it with a new column named Auto which is auto-incremented.

Which will look like this:

Name       Description        Auto
John        Student            1
Dom         Teacher            2

Current Code: SELECT * INTO table2 FROM table1

Answer 1

Use ROW_NUMBER to add sequential number starting from 1.

SELECT *,
    Auto = ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
INTO table2 
FROM table1

Answer 2

The accepted answer has additional convenience when breaking one table into several smaller ones with the exact number of rows. If necessary, it is possible to remove the column used for autoincrement.

SELECT *,
    ID = ROW_NUMBER() OVER(ORDER BY ( SELECT NULL ))
INTO #Table2
FROM Table1

DECLARE @start INT, @end INT;
SET @start = 1;
SET @end = 5000000;

SELECT *
INTO Table3
FROM #Table2
WHERE ID BETWEEN @start AND @end;

ALTER TABLE Table3 DROP COLUMN ID;       

Answer 3

You can use an identity field for this, that's what they're for. The logic of the identity(1,1) means that it will start at the number 1 and increment by 1 each time.

Sample data;

CREATE TABLE #OriginalData (Name varchar(4), Description varchar(7))
INSERT INTO #OriginalData (Name, Description)
VALUES
('John','student')
,('Dom','teacher')

Make a new table and insert the data into it;

CREATE TABLE #NewTable (Name varchar(4), Description varchar(7), Auto int identity(1,1))
INSERT INTO #NewTable (Name, Description)
SELECT 
Name
,Description 
FROM #OriginalData

Gives the results as;

Name    Description Auto
John    student     1
Dom     teacher     2

If you ran the insert a couple more times your results would look like this;

Name    Description Auto
John    student     1
Dom     teacher     2
John    student     3
Dom     teacher     4
John    student     5
Dom     teacher     6