When we talk about swapping of data between two columns of a table in SQL Server, most of us think of writing SQL scripts in Stored Procedure to achieve it. Well, is it really required? Can a one line statement be written to swap data between two columns in a table? The answer is yes. In this article I cover a simple technique to swap data between two columns of a table.
Consider a table by name "MyTable" having 3 fields RecID as Integer, ColValue01 as VarChar of 50 and ColValue02 as VarChar of 50; as written in the below SQL statement
CREATE TABLE [MyTable] (RecID INT, ColValue01 VARCHAR(50), ColValue02 VARCHAR(50))
Now due to some program error, the data which was suppose to go to ColValue02 has gone to ColValue01 and Vise-a-Versa. We need to swap the data between the two columns, run the below script to achieve the same.
#Option 01: The process is simple, first declare a temp variable, move column 2 value to temp variable, then move column 1 value to column 2 and finally move temp variable value to column 2.
DECLARE @MyTemp AS VARCHAR(50)
UPDATE [MyTable] SET @MyTemp = ColValue02, ColValue02 = ColValue01, ColValue01 = @MyTemp))
#Option 02: The same result can be achieved without using a temp variable by simply cross assigning the column names as shown the scrip below.
UPDATE [MyTable] SET ColValue01 = ColValue02, ColValue02 = ColValue01))
Note: The column data can be exchanged between two or more columns of same data type only.