Search code examples
sql-serverdatabasesql-updatesubstringsp-rename

How to combine an update and sp_rename in SQL Server database

I have a SQL Server database employeedb and table employee with column EmployeeID whose value I need to update and simultaneously rename that column.

EmployeeID
-----------
83456785647

I need to add a '-' 2 digit places from the end and rename EmployeeID to EmployeeNr to get the below output

EmployeeNr
------------
834567856-47

I can achieve the desired outcome in step wise by running two separate SQL statements as:

update employee 
set EmployeeID = substring(cast(EmployeeID as varchar(255)), 1, len(cast(EmployeeID as varchar(255)))-2) + '-' + right(cast(EmployeeID as varchar(255)), 2)

and to change the column name I execute a separate query as:

USE employeedb;
GO

EXEC sp_rename 'employee.EmployeeID', 'EmployeeNr', 'COLUMN';
GO

Is there a way to combine both statements in a single SQL statement?

Solution

  • I would, instead, suggest that you don't do either an UPDATE or rename the column; the ID is likely a primary key and being using for referencial integrity so changing its value and renaming it will likely break a wealth of things.

    Instead add the "Nr" column as a calculated column:

    ALTER TABLE dbo.Employee
ADD EmployeeNr AS STUFF(EmployeeID, LEN(EmployeeID)-1,0,'-');

    I also switch to STUFF to "inject" the character, as it seems a cleaner solution.