Post reply

Is it possible to change the schema of a column so that it is two columns?

  • November 22, 2005 at 5:15 pm

    #95743

    I have a column in the middle of a table that I would like to split into two columns. 

    For example, if below is part of the schema of the table

          bird char(1) NULL,

          cat char(20) NULL,

          dog char(1) NULL

    I would like to split the column cat, so that the schema would become

          bird char(1) NULL,

          cat char(1) NULL,

          mouse char(19) NULL,

          dog char(1) NULL

    I know, I can create a new table then use substring to select the values from the old table and insert the values into the new table.  I was hoping there might be a way, I could do an alter table statement or something similar.  I know this is an odd question.  But the system I am working with is rigid and the columns must be kept in a certain order.  For instance, I can not just simply alter the table to add column mouse to the end table.  I am trying to find a faster way to update the table, instead of creating a new table and selecting into the table.  Some of the tables, I will be updating have more than half a million rows.  If anyone has any suggestions for the fastest way to convert the data, please let me know.  Thanks!

  • November 22, 2005 at 8:20 pm

    #605381

    Rename the table and then create a view named as old table name. Be sure to include the "with VIEW_METADATA" option so that the application cannot see the underlying table.

    Tested with SQL Server 2000 SP3 (8.00.760)

    Create table ScrewyApp

    (bird char(1) NULL,

    cat char(20) NULL,

    dog char(1) NULL

    )

    go

    Alter table ScrewyApp

    add mouse char(19) NULL

    go

    update ScrewyApp

    set mouse = substring(cat,2,19)

    , cat = substring(cat,1,1)

    go

    alter table ScrewyApp

    alter column cat char(1)

    go

    sp_Rename 'ScrewyApp','ScrewyApp2'

    go

    create view ScrewyApp

    with VIEW_METADATA

    as

    select bird , cat , mouse, dog

    from ScrewyApp2

    WITH CHECK OPTION

    go

    insert into ScrewyApp

    (bird , cat , mouse, dog)

    values ('b','c','mouse19','d')

    go

    -- notice the different order of the columns

    select * from ScrewyApp

    select * from ScrewyApp2

    SQL = Scarcely Qualifies as a Language

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply