I need to update a column of type varchar such that the EmpName is set to EmpName1, EmpName2, EmpName3...for all rows.

Here's what I have tried but it doesn't work:

declare @i int
declare @empprefix varchar(20)
declare @i2 varchar(20)
set @i = 0
set @empprefix = 'EmpName'
set @i2 = cast(@i as varchar(20))
while @@ROWCOUNT > 0
update dbo.employee
set EmpName = @empprefix+@i2
set @i=@i+1
  • Not sure what more I can offer to the question. – supergirldba Oct 8 at 11:56
  • Try adding steps to create a table with test data. Then expected output. – Anthony Genovese Oct 8 at 13:47

You could use a common table expression and the ROW_NUMBER window function to assign a row number that could be used as the increment. I'm assuming the employee table has some kind of unique column that we could use to order by for the ROW_NUMBER window function. I'm using id in this example.

--demo setup
Declare @Employee table (id int, EmpName varchar(100))
insert into @Employee(id,EmpName) values(1,''),(2,''),(3,'')

--common table expression to assign a row number to each employee row ordered by id
;WITH _EmployeeWithRowNumber
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            ORDER BY id
            ) AS rn
    FROM @Employee
--update empname to include row number
SET empname = 'EmpName' + convert(VARCHAR(10), rn)
FROM _EmployeeWithRowNumber e

--verify results
FROM @Employee

| id | EmpName  |
| 1  | EmpName1 |
| 2  | EmpName2 |
| 3  | EmpName3 |
  • That get's me close enough. I just have to modify the select statement to get unique rows but this does the trick! Thanks! – supergirldba Oct 8 at 12:21
  • @supergirldba - That's awesome - you're welcome! :) – Scott Hodgin Oct 8 at 12:23

Your query seems to begin infinity loop, and no commit required without begin tran.

However, If you want update existing rows in Employee table, you probably want to try this:

update e set e.EmpName = (e.EmpName + Cast(ROW_NUMBER () OVER (ORDER BY e.EmpName) as varchar(5)) ) 
from  dbo.employee as e 

  • You are correct. I was testing so I probably didn't include exactly what I was running. Thank you. – supergirldba Oct 8 at 12:22

"+" is not the valid operator to concatenate strings (or varchar) - ...

check https://docs.microsoft.com/de-de/sql/t-sql/functions/concat-transact-sql?view=sql-server-2017

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.