How to Increment a Row?

Question

Post reply

How to Increment a Row?

Charmer

SSChampion

Points: 14903
More actions
December 5, 2011 at 1:11 am

#247882

Hi Frndz,
I have 2 columns name JurisID and CounterId...
My Output must be like this....
JurisID CounterId
1275 127500000000001
1276 127600000000002
1277 127700000000003
1275 127500000000004
1279 127900000000005
1279 127900000000006
For CounterID I am using Script Component to increment the row, it depends upon the Count of JurisID Column...but my problem is ,my logic for CounterID in script component takes the 1st row of JurisID and starts incrementing....so my output is now like
JurisID CounterId
1275 127500000000001
1276 127500000000002
1277 127500000000003
1275 127500000000004
1279 127500000000005
1279 127500000000006
i need the output like the above...how can i get that? help me frnds...
Thanks,
Charmer

Viewing 6 posts - 1 through 6 (of 6 total)

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

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

The JurisID column, is that one already calculated or do you do it at the same time?

Why don't you take the current value of the JurisID column, instead of the first one?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Your question is not very clear. Next time instead of writing the rows please write a small script that creates the table and inserts the data and also explain what you are trying to do.

If I understood your question, you just want to modify the value of column CounterID so it will include the value of column JurisID. If I did understand you correctly, then bellow is a code that does that.

create table #Demo (JurisID int, CounterId bigint)

go

INSERT INTO #Demo (JurisID, CounterId)

select 1275, 127500000000001

union select 1276, 127500000000002

union select 1277, 127500000000003

union select 1275, 127500000000004

union select 1279, 127900000000005

union select 1279, 127500000000006

go

select JurisID, cast(JurisID as char(4)) + right(cast(CounterId as char(15)), 11)

from #Demo

go

drop table #Demo

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Charmer SSChampion Points: 14903 More actions · Answer 3

that's what my problem is...it takes the 1st row instead of current value of JurisID...

i don't know where i am missing logic ....

below is what i am using in Script component to increment the row...

long iMaxID = 0;

public override void Input0_ProcessInputRow(Input0Buffer Row)

{

if (iMaxID == 0)

iMaxID = Row.MaxId;

Row.TxtID = iMaxID;

iMaxID = iMaxID + 1;

}

Row.maxid = maximum value from another table.

before script comp..i am using derived column to join the

Thanks,
Charmer

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Charmer,

how are you dealing with concurrency concerns here, IE: Two people are doing the same thing at once, both receive the value (for example) 00004081, and they both try to write record 00004082?

This is why the Identity() component was built, to avoid manual overcoding to deal with scenarios like this.

Perhaps I'm not understanding this properly, do you want a count to restart per JurisID?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Charmer SSChampion Points: 14903 More actions · Answer 5

Evil Kraig F (12/5/2011)
Charmer,
how are you dealing with concurrency concerns here, IE: Two people are doing the same thing at once, both receive the value (for example) 00004081, and they both try to write record 00004082?
This is why the Identity() component was built, to avoid manual overcoding to deal with scenarios like this.
Perhaps I'm not understanding this properly, do you want a count to restart per JurisID?

there will be no concurrency concerns over here....and i don't want to restart the counter....

i just figured out where my problem was...

my problem was not with the counter....it was with the derived column transformation...

i use derived column to get this kinda result for every jurisid..

JurisID(4) + CounterID(11) = TXTID(15) (primary key column in destination table)

i had used script component after derived column..so that's where i made a mistake...now i used derived column after the counter(script component) gets started to incremented...so i got what i was expecting....

Thanks,
Charmer