Swapping Of Records by column in Sqlserver

At times, we may accidently insert data into a wrong column if we have two identical columns (both having the same data type). To fix this later on we can delete the data from the wrong column and insert it in the proper one, but that becomes a lot of work if you know how to swap data between the two columns through SQL.
Swapping can be done :
                              
             1.with the help of a temporary variable .
    
                      or
      
              2.directly.
 
Suppose we have a table named Employee with 3 columns: ID, FirstName, LastName.
       
              ID: int 
              FirstName: varchar(10)
              LastName: varchar(10)
 
and we have inserted some rows to the table.
 
              Insert into Emplyee values(1,’Kumar’,’Mahesh’)
 
              Insert into Emplyee values(2,’Kumari’,’Abhilasha’)
 
              Insert into Emplyee values(3,’Kumar’,’Anand’)
 
              Insert into Emplyee values(4,’Kumar’,’Binit’)
 
Here we have wrongly inserted LastName in place of FirstName and we need to swap them.
1. Swap with the help of temporary variable:
————————————————-
DECLARE @temp AS varchar(10)
 
UPDATE Employee1 SET @temp=LastName, LastName=FirstName, FirstName=@temp
 
  The resulting  table can be verified.
 
2.directly:
————

UPDATE Employee1 SET LastName=FirstName, FirstName=LastName
 
This query can also swap the column data.
 
Now we can see the difference by :
 
      Select ID,FirstName, LastName from Employee
 
This swapping be done for more than two columns also. Suppose we need to swap three columns named value1,value2,value3
 
UPDATE Employee SET value1=value3, value2=value1, value3=value2
 
So like this we can swap column data. Hope freshers  will find it helpful . : )
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s