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

Solution 1

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.

Solution 2

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

Solution 3

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

Comments

[Kaf]

select into creates a new table. If the table already exists then it could be an issue.

[Robert]

Yes, I know, but I added one more solution how to create table correctly.