Creating a count field in DTS/T-SQL.

Question

Creating a count field in DTS/T-SQL.

matty_p75

SSCommitted

Points: 1708
More actions
July 10, 2003 at 4:10 am

#160144

I need to update a field in a table with an id (or count of) values in another field. For example I have a field with a postcode/zip code in it, however there are multiple zip codes on the file. For the first zip code I need to put a 1 in this field, the second record a 2 and so on. Is there any way I can do this in a DTS T-SQL script? Or any other DTS task for that matter. I'm fairly new to SQL server and as this seems not to be an obvious SQL type process I'm not sure how to go about it in DTS. I could do it in a language like VB, so maybe using an ActiveX script task is a way of doing this. However I find that a little alien at the moment. Any help would be greatly appreciated,
thanks,
Matt

Viewing 4 posts - 1 through 3 (of 3 total)

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

David Burrows SSC Guru Points: 65143 More actions · Answer 1

Do you like this

create table #pc (idno int,p varchar(8))

insert into #pc values (0,'AA1 BB1')

insert into #pc values (0,'AA1 BB1')

insert into #pc values (0,'AA1 BB1')

insert into #pc values (0,'AA2 BB2')

insert into #pc values (0,'AA2 BB2')

insert into #pc values (0,'AA2 BB2')

insert into #pc values (0,'AA2 BB2')

insert into #pc values (0,'AA2 BB2')

insert into #pc values (0,'AA3 BB3')



declare @idno int,@p varchar(8)

set @idno=0

set @p=''

update #pc set @idno=(case when p<>@p then 1 else @idno+1 end),idno=@idno,@p=p

select * from #pc



idno        p        

----------- -------- 

1           AA1 BB1

2           AA1 BB1

3           AA1 BB1

1           AA2 BB2

2           AA2 BB2

3           AA2 BB2

4           AA2 BB2

5           AA2 BB2

1           AA3 BB3

Far away is close at hand in the images of elsewhere.
Anon.

matty_p75 SSCommitted Points: 1708 More actions · Answer 2

matty_p75

SSCommitted

Points: 1708

July 10, 2003 at 7:56 am

#464858

Many thanks, that works a treat!!!

rstone SSCertifiable Points: 6213 More actions · Answer 3

Make sure the table is ordered by the column in question unless you want the count to reset. The UPDATE statement does not permit the order by clause. Perhaps either a clustered index (e.g., "create clustered index [IX_#pc] ON #pc (p asc))" or an updateable view (with an order by clause) can be used.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉