I'm really sorry. my mistake on previous question. I re-write my question as following,I've table and data as following,CREATE TABLE [dbo].[myRunnNum]( [idx] [int] IDENTITY(1,1) NOT NULL, [myCode] [varchar](10) NOT NULL, [runnNum1] [int] NOT NULL, CONSTRAINT [PK_myRunnNum] PRIMARY KEY CLUSTERED ( [idx] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY];insert into myRunnNum values('trnxNo',9);Let's say, my transaction as following,declare @tItem table(idx int identity, trnxNo int, payer int, amt decimal(10,2));/*trnxNo is unique*/declare@data xml set@data='<data> <paymentItem><payer>1</payer><amt>10.00</amt></paymentItem><paymentItem><payer>2</payer><amt>16.00</amt></paymentItem><paymentItem><payer>6</payer><amt>35.00</amt></paymentItem><paymentItem><payer>23</payer><amt>40.00</amt></paymentItem></data>'
i need to insert into @tItem(trnxNo, .....). my TrnxNo is generated from myRunnNum(runnNum1) where myCode='trnxNo', which is myRunnNum is running number tableWe've to make sure, there's no duplicate value is generated for trnxNoI'm stuck, how to generate the runnNum1 in myRunnNum and insert into @tItem(trnxNo) with XML dataMy expected result as following,@tItemidx | TrnxNo | payer | amt-------------------------------1 10 1 10.002 11 2 16.003 12 6 35.004 13 23 40.00myRunnNumidx | code | runnNum1---------------------------1 trnxNo | 13
need help to generate number from running no table
Posted - 2011-07-31 : 01:34:33
