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
Use ROW_NUMBER to add sequential number starting from 1.
SELECT *,
Auto = ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
INTO table2
FROM table1
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;
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
