How to duplicate an entire column with values with Microsoft SQL Server?

Question

I have a column in a table of +15000 entries - I want to duplicate that entire column while retaining it's values and name it differently. I'm using Microsoft SQL Server Management Studio.

How do I go about that?

Thanks.

Answer 1

Create a new column and then

UPDATE YourTable
SET NewColumn = OldColumn

Answer 2

ALTER TABLE Boo
Add [NewColumnName] [datetype]
GO

Update Boo
Set NewColumnName = OldColumn
GO

Answer 3

INSERT into tbl (colname,fld1,fld2,fld3,fld4)  
 SELECT 'NEWNAME' as colname, fld1,fld2,fld3,fld4  
 FROM tbl where uniqid=[SOMEVAL]

Answer 4

Why not a computed column?

ALTER TABLE Foo ADD NewColumnName AS OldColumnName

This alos applies of you want to change a number into text for example

ALTER TABLE Foo ADD NewColumnName AS RIGHT('ABC000000' + CAST(OldColumnName AS varchar(10)), 10)

1       ABC0000001
2       ABC0000002
3       ABC0000003
4       ABC0000004
5       ABC0000005

Answer 5

BEGIN TRANSACTION

   ALTER TABLE myTable
       Add newcol NVARCHAR(8)
   GO 

   UPDATE myTable
       Set newcol = oldcol
   GO 

COMMIT TRANSACTION

Replace NVARCHAR(8) with whatever you current columns data type is.

Answer 6

Based on your comments to the original question, I understand that you are trying to preserve 25 distinct values in ColumnA while converting the new ColumnB to hold 10 different values. I think you could do something like the code below. Modify my datatypes to match your situation.

declare @ColumnAConverter table (
    ColumnA nvarchar(max), 
    ColumnB nvarchar(max)
)

insert into @ColumnAConverter
    (ColumnA, ColumnB)
    select 'A', '1'
    union all
    select 'B', '1'
    union all
    select 'C', '2'
    /* ...continue for all 25 values in the original ColumnA */

update yt
    set ColumnB = c.ColumnB
    from YourTable yt
        inner join @ColumnAConverter c
            on yt.ColumnA = c.ColumnA