Hi!

I am new to DTS scripting and I was wondering if there's any way in DTS to
scan an existing table and change a field's value. I am generating a lookup
table as I load my raw data. As this lookup table will be used later on to
populate a dimension table, I need to programmatically scan its contents and
assign values to an empty (key) field.

Here's what I want to do in pseudo code:

i = 0
create table MYTABLE (key int, desc varchar(20))
insert into MYTABLE (select distinct 0, SomeDescField from MYSOURCETABLE)
scan MYTABLE
i = i + 1
MYTABLE.key = i
endscan

Any help will be higly appreciated. Thanks!

Greg

You could use an IDENTITY column:

CREATE TABLE #MYTABLE (key int IDENTITY (0,1), descr varchar(20))

insert into MYTABLE (descr)
select SomeDescField from MYSOURCETABLE



--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


"Greg_Del_Pilar" <Greg_Del_Pilar (AT) hotmail (DOT) com> wrote