CODEWITHSUNDEEP
sql-servercounter
tsionyx
tsionyx

Reputation: 1669

Add counter column to table

How can I insert values into table variable from another table by adding a column that counts original records. e.g.

 Value              Number    Value
----------  --->    ----------------
 56                 1         56
 78                 2         78
 90                 3         90

However, I cannot use an IDENTITY(1,1) to automatically generate counter values because I want to explicitly insert values into Number column. May be I should use CTE?

Upvotes: 0

Views: 9019

Answers (2)

Moyshe Zuchmir
Moyshe Zuchmir

Reputation: 1642

If you don't use 'ORDER BY' during your query, you may use:

SET @number=0;
SELECT @number:=@number+1 AS number, value FROM your_table;

Upvotes: 0

AnandPhadke
AnandPhadke

Reputation: 13486

select row_number() over (order by (select 0)) as number,value from @tablevariable

Upvotes: 4

Related Questions