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