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 