This article is for newbie's who have just started their career in SQL development. Everyone might have faced problems while importing or inserting data into tables. One of the problems can be one column data goes into another column accidentally.

I have faced such problem while importing data from one table into another table which has different field names and accidently I inserted some other columns data into a different column. There was just a minor difference in the column names and all of a sudden many things went wrong.

Whenever such a problem arises, we often become tense and the first solution that comes into our mind is to make another temporary column and using the same old logic for swapping two values: set c = a, a = b, b= c. Then drop the new column, This article would help you learn to interchange the column values without declaring another temporary column.

Say, we have a table named "SwapData"

CREATE TABLE SwapData(id int,value1 varchar(50),value2 varchar(50))
go

Let us insert some values in the table above:

INSERT INTO Swapdata
SELECT 1,10,100
UNION ALL
SELECT 2,20,200
UNION ALL
SELECT 3,30,300
UNION ALL
SELECT 4,40,400
UNION ALL
SELECT 5,50,500
UNION ALL
SELECT 6,60,600
UNION ALL
SELECT 7,70,700
UNION ALL
SELECT 8,80,800
UNION ALL
SELECT 9,90,900
UNION ALL
SELECT 10,11,110

This will insert ten rows having different values in "value1" and "value2".

Now the problem is that the values "value1" and "value2" are in the wrong columns. We must interchange these with each other, putting the data in "value1" in the "value2" column and vice versa.

DECLARE @temp AS varchar(50)
UPDATE swapdata SET @temp=value2,value2=value1,value1=@temp

Instead of declaring a temporary column, we can declare a temporary variable, and it is possible to swap the data of two columns. This statement will first take the value of column "value2" in @temp variable and then take the data of " value1" column in "value2" and finally take the value of @temp in "value1". It's so simple and a much less time consuming way of swapping the data values. Now try selecting the rows of the table.

SELECT * FROM swapdata

I found another solution as well, without the use of any temporary variable. Just execute the following T-SQL and it will swap the column's data.

UPDATE swapdata SET value2=value1,value1=value2

I have tried the same solution for three columns. Let's add a third column "value3" in the "swapdata" table.

ALTER TABLE swapdata ADD value3 varchar(50)
go
UPDATE  swapdata SET value3=value2+'0'
SELECT * FROM swapdata

Let's interchange the values of column as:

"value1" to "value2"

"value2" to "value3"

"value3" to "value1"

We will follow the second solution for attaining the result above.

UPDATE swapdata SET value1=value3,value2=value1,value3=value2

So, next time you face the problem of swap column's data don't get tensed and make the problem worse. This even can be used for swapping data of more number of columns.