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.