July 30, 2011 at 10:57 pm
I've table and data as following,
CREATE TABLE [dbo].[myRunnNum](
[idx] [int] IDENTITY(1,1) NOT NULL,
[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'm stuck, how to generate the runnNum1 in myRunnNum and insert into @tItem(trnxNo). So far, I've
insert into @tItem
select ????,
a.b.value('payer[1]','int'), --as input1,
a.b.value('amt[1]','decimal(10,2)') --as input2
from
@data.nodes('/data/paymentItem') a(b)
My expected result as following,
@tItem
idx | TrnxNo | payer | amt
-------------------------------
110110.00
211216.00
312635.00
4132340.00
myRunnNum
idx | code | runnNum1
---------------------------
1 trnxNo | 13
July 30, 2011 at 11:57 pm
Something like this
BEGIN TRAN
DECLARE@RunnNum INT = (SELECT RunnNum1 FROM MyRunnNum WHERE Code = 'trnxNo');
INSERT@tItem
(
trnxNo,
payer,
amt
)
SELECT@RunnNum + ROW_NUMBER() OVER (ORDER BY a.b),
a.b.value('payer[1]', 'INT'),
a.b.value('amt[1]', 'DECIMAL(10, 2)')
FROM@data.nodes('/data/paymentItem') AS a(b)
UPDATEMyRunnNum
SETRunnNum1 += @@ROWCOUNT
WHERECode = 'trnxNo'
COMMIT TRAN
N 56°04'39.16"
E 12°55'05.25"
July 31, 2011 at 12:07 am
tq sir.
it's work and your solution really great. but, how to prevent another transaction is updated the myRunnNum till I'm finish?
July 31, 2011 at 12:47 am
That is what the transaction is for.
N 56°04'39.16"
E 12°55'05.25"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply