SQL SERVER – Swap Column Values In Table

Some questions never got old and today’s we are going to discuss a similar question which was asked to me during Comprehensive Database Performance Health Check. The question was about how to swap column values in a table.

SQL SERVER - Swap Column Values In Table swap-columns-800x256

Let us first create a table with two columns and insert values in it.

CREATE TABLE TestTable (Col1 INT, Col2 INT);
INSERT INTO TestTable (Col1, Col2)
VALUES (1,2);
SELECT Col1, Col2
FROM TestTable;

In the table, there are two values 1 and 2 respectively in the Col1 and Col2.

The next task is to swap those values. If you are a developer and have learned the programming language in the class, you would think that you will need a third variable or a temporary storage location to swap the third value. However, if you are a SQL Server user, you can simply swap them using a single update statement.

SWAP Column

UPDATE TestTable
SET Col1 = Col2, Col2 = Col1;

When you run above update statement, the values of the columns will be swapped in SQL Server. There is no need for temporary column, variable or storage location in SQL Server. You can validate that with the SELECT statement here.

SELECT Col1, Col2
FROM TestTable;

Here is the final script to drop the table which you had created.

DROP TABLE TestTable;
GO

If you have any other interesting SQL Server Tip, do send me an email or leave a comment and I will be happy to post them on the blog with the due credit to you.

Here are my recent SQL in Sixty Seconds Video, let me know your thoughts about the same.

To understand what actually CHECKPOINT and DBCC DROPCLEANBUFFERS do first we need to understand a couple of more concepts which are what is a clean buffer and dirty buffer in memory. I suggest you read the following blog post before continuing reading this blog post.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

SQL Column, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Microsoft Official Support End Dates for Different Versions
Next Post
SQL SERVER – Enable or Disable Resource Governor

Related Posts

2 Comments. Leave new

  • Thanks for this article, above you have mentioned to swap values. So, UPDATE TestTable
    SET Col1 = Col2, Col2 = Col1; here the first statement is col1 = col2 (means we have assigned col2 value to col1) suppose we have VALUES (1,2); assigned 1=2. Now what is the value of col2 is (2) the second statement
    col2 = col1 in previous statement we have changed the value already of col1, so still we have same value of col2. Below are my example.

    create table Test(id integer, sal integer);
    insert into Test(id, sal) values(1, 12);
    insert into Test(id, sal) values(2, 13);

    UPDATE Test
    SET id = sal, sal = id;

    select id, sal from Test;

    output:

    id sal
    12 12
    13 13

    Reply
  • How we replace if One column is Integer and other one is string / nvarchar ???

    Reply

Leave a Reply