Copying columns in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copying columns in query

Author

Topic

piskovic
Starting Member

7 Posts

Posted - 2006-12-29 : 03:50:03

I know this is no problem for anyone who has ever done a bit of programming in SQL, but for me it is a problem because I'm rather new in SQL. My question is: how to copy column in the same table in query? I need move one column to another and then to delete the first column (but not the whole column, just one part so I can't use Copy - Paste in Enterprise Manager). I know how to select the part that I want, all I need now is to copy columns.

Thank You all in advanced!

Sincerely,

Ivan

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-29 : 03:59:53

Use ALTER TABLE to add the new column, then run an update to copy column values to the new column, then use ALTER TABLE again to drop the old column. Something like this

ALTER TABLE YourTable
ADD NewCol YourDataType

UPDATE YourTable
SET NewCol = OldCol
WHERE your expression 

ALTER TABLE YourTable
DROP COLUMN OldCol

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-29 : 07:59:50

"(but not the whole column, just one part so I can't use Copy - Paste in Enterprise Manager)"

You mean to say you are not dropping the column? Just transferring data to another column and setting the values in the original column to NULL?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-31 : 23:43:50

<<
but not the whole column, just one part so I can't use Copy
>>
What do you mean by that?

Madhivanan

Failing to plan is Planning to fail

sshelper
Posting Yak Master

216 Posts

Posted - 2007-01-01 : 23:14:57

Maybe what you mean is that you just want to copy the contents of certain records from one column to another but not all records. If this is the case, then you simply need to do an UPDATE with a WHERE clause as pointed out by snSQL in his query:

UPDATE YourTable
SET Column1 = Column2
WHERE <Your WHERE Condition Here>

Since you also mentioned you want to "move one column to another and then delete the first column", you have to do another UPDATE setting the value to NULL:

UPDATE YourTable
SET Column2 = NULL
WHERE <Your WHERE Condition Here>

SQL Server Helper
http://www.sql-server-helper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources