Swap SQL Server column data due to inserting data into wrong columns

By: Divya Agrawal   |   Updated: 2009-04-24   |   Comments (4)   |   Related: > TSQL

Problem

This article is for newbies who have just started their career in SQL development.  Everyone might have faced this problem when importing or inserting data into tables when they accidentally put the data in the wrong columns. The first thought would be to delete the data and start over, but in this tip we look at a simple solution to swap the data. 

Solution

To illustrate this problem I am going to create a simple table with three columns that have the same data type as shown below.

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

Now that the table has been created, let's insert some values in the table. Here I am using a UNION ALL statement so I can do one INSERT, but this could also be done by issuing several insert statements.

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" as shown in the image below.

Now that are data is inserted we can see that the larger values should have been in "value1".  So now we need to swap the values of "value1" and "value2".

There are various solutions to this problem. As discussed in the problem statement you could delete the data and start over again or you could create a new column and move the data, but there are easier options.

One of these easier options is shown below where a temporary variable is created which then allows you to update the data and swap the column values.

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

Instead of creating a temporary column in the table. this can be done by declaring a temporary variable to swap the data in the two columns.  The code above is explained below.

• First create a temporary variable
• Then put the data of column "value2" in the @temp variable
• Then put the data of column "value1" in the "value2" column
• Finally take the value of @temp and put it in "value1" column

Now, try selecting the rows of the table.

SELECT * FROM swapdata

And we can see that our table has been updated correctly.

But the solution which I found the best is without the use of any temporary variable.

Just execute the following T-SQL and it will swap the columns data. 

UPDATE swapdata SET value2=value1,value1=value2

This moves the data from value1 to value 2 and the data from value2 to value1 at the same time.

Now, try selecting the rows of the table and we can see the data has been swapped back to the original.

SELECT * FROM swapdata

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 the columns as:

• "value1" gets "value3" data
• "value2" gets "value1" data
• "value3" gets "value2" data

We will follow the second solution for attaining the result.

UPDATE swapdata SET value1=value3,value2=value1,value3=value2
  
SELECT * FROM swapdata

Note that the series of swapping columns can be given in any order. Just make sure the value is passed as per the requested output. I think this would be very helpful to everyone, just interchanging column values without having the headache of temporary columns and variables.

So next time you face the problem of having to swap data among columns in a table just remember this simple solution.

Next Steps

• This can be used for more columns as well and also by joining multiple tables.
• It can even be used by adding WHERE conditions in the update statement, so that whenever the data for a particular range has to be swapped it is possible

About the author

Divya Agrawal's bio is coming soon...

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips