0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
GKC
  • 447
  • 4
  • 10
  • 1
    No, you are performing DML then DDL they are separate operations and presumably this is a one-off task...? – Stu Jun 26 '23 at 17:41
  • Yes it is a once-off task but I have many tables where I need to execute the queries with each table running into several millions of records. Thanks. – GKC Jun 26 '23 at 17:53
  • 1
    Perhaps what you actually want is a computed column with that ID in it? Then you can just `ADD` the column and as it's computed, the values will be determined when needed. – Thom A Jun 26 '23 at 17:54
  • You can do both in a transaction if you wish. – Charlieface Jun 26 '23 at 20:50
  • @Charlieface : How can I possibly do that? – GKC Jun 26 '23 at 22:07
  • `SET XACT_ABORT ON; BEGIN TRAN; UPDATE ...; EXEC sp_rename ..; COMMIT;` – Charlieface Jun 26 '23 at 23:16

1 Answers1

1

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75