Menu
I wanna update first and last name of one table with incremental value. For example:-
ID FirstName
1 Demo_01
2. Demo_02
3. Demo_03
4. And so on....
This table has 1000s of records. But for demo purposes we do not want to share real Name. So Please help how to update First Name with "Demo_ + Incremental value by one?
283
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) .
An Incremental update adds new records to a project data set from a source Hive table.
+= (Addition Assignment) (Transact-SQL)Adds two numbers and sets a value to the result of the operation. For example, if a variable @x equals 35, then @x += 2 takes the original value of @x, add 2 and sets @x to that new value (37).
If you're looking to add auto increment to an existing table by changing an existing int column to IDENTITY , SQL Server will fight you. You'll have to either: Add a new column all together with new your auto-incremented primary key, or. Drop your old int column and then add a new IDENTITY right after.
;with cte as
(
SELECT FirstName, ROW_NUMBER() OVER (ORDER BY ID) RN
FROM YourTable
)
UPDATE cte
SET FirstName = 'Demo_ ' + CAST(RN AS VARCHAR(10))
Or do you mean you want to use the ID field directly?
UPDATE YourTable
SET FirstName = 'Demo_ ' + CAST(ID AS VARCHAR(10))
NB: You say you have thousands of records but obviously they won't fit in the Demo_01 format. Say you want to allow up to 6 digits and pad with leading zeroes you could use something like.
UPDATE YourTable
SET FirstName = 'Demo_ ' + RIGHT('000000' + CAST(ID AS VARCHAR(6)),6)
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
