Mass update of data in sql from int to varchar

We have a large table (5608782 rows and growing) that has 3 columns Zi开发者_JAVA技巧p1,Zip2, distance

All columns are currently int, we would like to convert this table to use varchars for international usage but need to do a mass import into the new table convert zip < 5 digits to 0 padded varchars 123 becomes 00123 etc. is there a way to do this short of looping over each row and doing the translation programmaticly?

A simple RIGHT will do it in SQL Server

INSERT
    newTable (...
SELECT
    RIGHT('00000' + CAST(Zip AS varchar(5)), 5),
    ...
FROM
    OldTable

The following example is assuming you are using MS SQL Server. See here for help on the replicate function.

insert  NewZipTable
        (Zip1,
        Zip2,
        Distance)
select  replicate('0', 5 - len(Zip1)) + CAST(Zip1 as VARCHAR(10)),
        replicate('0', 5 - len(Zip2)) + CAST(Zip2 as VARCHAR(10)),
        Distance
from    OldZipTable

for SQL Server....

try this, assumes nvarchar(20) for the new column:

INSERT INTO NewTable
       (col1, col2, col3
       ,NewZip)
    SELECT
        col1, col2, col3
        ,RIGHT(REPLICATE(N'0',20)+CONVERT(nvarchar(20),oldZip),20) --null will result in null
        FROM OldTable

In MySQL you could do this:

INSERT INTO newTable (zip1, zip2, distance) 
SELECT LPAD(zip1, 5, '0'), LPAD(zip2, 5, '0'), distance
FROM oldTable;

Don't do such things in a loop, they can usually be done using pure SQL with much better performance.

Also, always store zip-codes and other things that can't be used for calculations as characters. Never heard of a 10% zip-code increase :)

Example using Oracle:

Insert Into new_table (zip1, zip2, distance)
    Select
        TO_CHAR( zip1, 'FM00000' ),
        TO_CHAR( zip2, 'FM00000' ),
        distance
    From old_table