sql server dts: How do I scan records and assign values to fields using DTS? -- sql server dts

How do I scan records and assign values to fields using DTS?

Greg_Del_Pilar
7/6/2003 11:08:17 PM

sql server dts:

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

Re: How do I scan records and assign values to fields using DTS?

Jacco Schalkwijk
7/7/2003 11:10:42 AM

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.

[quoted text, click to view]