How to add a row number to new table in SQL?

Question

I'm trying to create a new table using an existing table already using:

INSERT INTO NewTable (...,...)
   SELECT * from SampleTable

What I need to is add a record number at the beginning or the end, it really doesn't matter as long as it's there.

Sample Table

Elizabeth  RI 02914
Emily      MA 01834

Prospective New Table

1 Elizabeth  RI 02914
2 Emily      MA 01834

Is that at all possible?

This is what I ultimately I'm shooting for... except right now those tables aren't the same size because I need my ErrorTemporaryTable to have a column in which the first row has a number which increments by the previous one by one.

declare @counter int
declare @ClientMessage varchar(255)
declare @TestingMessage carchar(255)
select @counter = (select count(*) + 1 as counter from ErrorValidationTesting)
while @counter <= (select count(*) from ErrorValidationTable ET, ErrorValidationMessage EM where ET.Error = EM.Error_ID)
begin
    insert into ErrorValidationTesting (Validation_Error_ID, Program_ID, Displayed_ID, Client_Message, Testing_Message, Create_Date)
    select * from ErrorTemporaryTable

    select @counter = @counter + 1

end

score 9 · Answer 1 · edited May 23 '17 at 12:00

9

You can use into clause with IDENTITY column:

SELECT IDENTITY(int, 1,1) AS ID_Num, col0, col1
INTO NewTable
FROM OldTable;

Here is more information

You can also create table with identity field:

create table NewTable
(
  id int IDENTITY,
  col0 varchar(30),
  col1 varchar(30)
)

and insert:

insert into NewTable (col0, col1)
SELECT col0, col1   
FROM OldTable;

or if you have NewTable and you want to add new column see this solution on SO.

edited May 23 '17 at 12:00

Community

1
1

answered Oct 24 '12 at 20:43

Robert

25,425
8
67
81

select into creates a new table. If the table already exists then it could be an issue. – Kaf Oct 24 '12 at 20:51
Yes, I know, but I added one more solution how to create table _correctly_. – Robert Oct 24 '12 at 20:53

score 9 · Answer 2 · answered Oct 24 '12 at 20:46

9

INSERT INTO NewTable (...,...)
SELECT ROW_NUMBER() OVER (ORDER BY order_column), * from SampleTable

answered Oct 24 '12 at 20:46

Aleksandr Fedorenko

16,594
6
37
44

score 2 · Answer 3 · answered Oct 24 '12 at 20:47

2

If you are in SQL Server

INSERT INTO newTable (idCol, c1,c2,...cn)
SELECT ROW_NUMBER() OVER(ORDER BY c1), c1,c2,...cn
FROM oldTable

answered Oct 24 '12 at 20:47

Kaf

33,101
7
58
78

score 2 · Answer 4 · answered Dec 03 '19 at 22:17

2

Try this query to insert 1,2,3... Replace MyTable and ID with your column names.

DECLARE @myVar int
SET @myVar = 0
UPDATE
  MyTable
SET
   ID =  @myvar ,
   @myvar = @myVar + 1

answered Dec 03 '19 at 22:17

live-love

48,840
22
240
204

How to add a row number to new table in SQL?

4 Answers4