Menu
I want to create an extra column while fetching data, and that column should increment values like
id marks myextcolumn
--------------------
1 89 1
2 99 2
4 67 3
6 77 4
.
.
.
483
Adding a new column as auto increment to the existing database table is not possible through the SQL Server Management studio (SSMS), so we do it by SQL Script. Open a new query window by clicking on the New Query icon from top left and run following command. Alter table PersonalDetails. Add AutoId int NOT NULL IDENTITY.
The only way to persist a auto increment is to add a new column to the base table with a IDENTITY value; there is no other way. The only way to persist a auto increment is to add a new column to the base table with a IDENTITY value; there is no other way.
In the above statement, you need to specify the table_name and column_name. Here’s the SQL statement to add AUTO INCREMENT constraint to id column. Next we will add a couple of rows in sales table. As you can see, the MySQL has automatically increased and populated id column with values 7 and 8.
The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table: ); 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.
You need to use row_number function
Schema:
CREATE TABLE #TAB (ID INT, MARKS INT)
INSERT INTO #TAB
SELECT 1 , 89
UNION ALL
SELECT 2 , 99
UNION ALL
SELECT 4 , 67
UNION ALL
SELECT 6 , 77
Do select the above table with Rownumber for Extra column
SELECT
ID, MARKS,
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) EXTRA_COL
FROM #TAB
The result will be
+----+-------+-----------+
| ID | MARKS | EXTRA_COL |
+----+-------+-----------+
| 1 | 89 | 1 |
| 2 | 99 | 2 |
| 4 | 67 | 3 |
| 6 | 77 | 4 |
+----+-------+-----------+
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
