Menu
I have a T-SQL select statement and I want to auto-increment a column in it (that doesn't exist in the database)
select dbo.a, dbo.b, dbo.c, select @d:=1; @increment:=@increment+1 AS d
Is this possible?
292
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
By default, the AUTO_INCREMENT starts with 1 and increases by 1. Example: We will create Students table with fields Student_ID, First_Name, Last_Name, we will auto generate Student_ID by using auto increment and will make it Primary Key for the table.
A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery.
Auto-increment is a concept in SQL which automatically generates the unique number in the field when the new row is entered into the table. This feature is generally used for the Primary Key field, where we need to create a unique value for every record.
Assuming you're using SQL 2005 or later:
SELECT dbo.a, dbo.b, dbo.c, ROW_NUMBER() OVER(ORDER BY GETDATE()) AS d
to order the rows as they are returned form the DB. If you want to specify an order you can do so:
SELECT dbo.a, dbo.b, dbo.c, ROW_NUMBER() OVER(ORDER BY dbo.a) AS d
For SQL 2000 and earlier you need a unique value to order by:
SELECT dbo.a, dbo.b, dbo.c, (SELECT COUNT(*) FROM dbo d2 WHERE d2.a <= dbo.a) AS d
FROM dbo
or if you don't need a single SELECT:
SELECT IDENTITY(int,1,1) ID, dbo.a, dbo.b, dbo.c
INTO #Temp
FROM dbo
SELECT * FROM #Temp
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
