Hi guys, can anybody help to solve this problem.
set @.count=0
Insert into User_t (userid, counter) select userid, count+1 from resultset is not working
0/p: bhasker 1
bhanu 1
kishore 1
but o/p must be
bhasker 1
bhanu 2
kishore 3
You can't do it with a query like that. Here are two alternatives
1. Declare the counter field as an identity integer (it'll increment by one each time)
or
2. cursor around the resultset, inserting one record at a time and incrementing by one within the loop.
|||Here's a neat trick.
Create a table variable with an additional column to store the counter value. And insert into the table from your SELECT. do an UPDATE on the Table variable as follows.
Declare @.TTable (useridvarchar(10), Counterint)Insert into @.TSelect'bhasker',0unionallSelect'bhanu',0unionallSelect'kishore',0select *from @.tDeclare @.iintSet @.i = 0Update @.TSet @.i = Counter = @.i + 1Select *from @.t|||
Have a look at this. But I think you can do somthing like this.
http://support.microsoft.com/kb/186133
select rank=count(*), a1.name from addresses a1, addresses a2
where a1.name >= a2.name
group by a1.name
order by rank
My exact question is:
INSERTINTO UserChargeDetail(UserID, Offering, Counter,OfferingDetail,
Comment, ValueInput, CostCenterInput,Month)
SELECT UserID, tierOrder,rowsequence,NULL,NULL, 1,'',GetDate()FROM CostCenter
how do I get rowsequence
rowsequence: 1,2,3,4,5
|||
You were shown at least 2 or more working solutions on getting the sequence number. Please put some effort in trying out those solutions. If you are still unable to resolve your issue, please post what you have tried so we can guide you into solving the issue.
