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, Progr…

Accepted Answer

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.

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

Solution

Problem

Related problems