Doing a ALTER and/or porrible UPDATE for a comma delimited......

[smileydba]

Newbie question,

Problem - Customer has a Unix system that spits out a ANSII file, and I already imported this into the MS-SQL database. The problem is that I need to add a additional value to the datatype to a column in a table (easy enough, do a alter table, alter column).
Here is the kicker (at least to me), I need to add the data so that I can add a 0 to the left side of the value in the column.
So, basically, if the origional value in the column is say, 1234, I need to add the value 0 to the left side, which would then be 01234(and this is to ALL the values). Would this be a update statement, or something else?????
Sorry for my newbie question, but Im kinda perplex on how to do this part.

Rob W.

 

[jdgonzalez]

There are two way to go about this (at least that I can think of).

1. Use active-x scripting in the ETL process to insert a '0' at the beginning of the data.

2. Load the data to a staging table then from there do something like

insert into prodTable
select '0' + cast(tmpField as varchar(10))
from stagingTable

 

[jdgonzalez]

...or you could also do:

update prodTable
set myField = '0' + myfield

This assumes that myField is set to varChar.