To resolve a problem, sometimes we start thinking at a high level while simple solutions of said problem are available. This is what happened to me when one
of my colleagues (Tehman) asked me how to swap two column values in a table.
My answer was, create a third column (temporary) and swap using this third column, which you can remove later on. Here was the plan.
- Move Col2 data to Col3
- Move Col1 data to Col2
- Move Col3 data to Col1
- Drop Col3
CREATE TABLE #ForSwappingTest ( Col1 VARCHAR(50), Col2 VARCHAR(50))
INSERT INTO #ForSwappingTest (Col1,Col2)
SELECT * FROM #ForSwappingTest
ALTER TABLE #ForSwappingTest ADD Col3 VARCHAR(50)
SET COL3 = COL2
SET COL2 = COL1
SET COL1 = COL3
ALTER TABLE #ForSwappingTest DROP COLUMN Col3
DROP TABLE #ForSwappingTest
But he came with a very simple solution, by writing the following simple query.
SET Col2 = Col1,
Col1 = Col2
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.