need help on T-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

need help on T-SQL

Author

Topic

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-07-30 : 02:47:44

I've table 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],
 CONSTRAINT [myRunnNum_UQ1] UNIQUE NONCLUSTERED 
(
	 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);

*myRunnNum is a running number table

Let's say, my transaction as following,

[code]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>'

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)


I'm stuck, how to generate the TrnxNo in @tItem

My expected result as following,
[code]@tItem
idx | TrnxNo   | payer  | amt
-------------------------------
1	10	1	10.00
2	11	2	16.00
3	12	6	35.00
4	13	23	40.00

myRunnNum
idx | code | runnNum1
---------------------------
1     trnxNo | 13

really need help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-30 : 04:05:33

something like


insert into @tItem
select (SELECT MAX([runnNum1]) FROM myRunnNum) + ROW_NUMBER() OVER (ORDER BY NEWID()) ,
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)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-07-30 : 04:09:33

the above code will lock the runnNum1 in myRunnNum?

i'm afraid another transaction will get the same value

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-07-30 : 04:11:31

my TrnxNo in @tItem is null. another problem is why myRunnNum is not updated?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-30 : 04:41:55

quote:
Originally posted by Idyana

the above code will lock the runnNum1 in myRunnNum?

i'm afraid another transaction will get the same value

what do you mean by lock the runnNum1 in myRunnNum?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-07-31 : 00:27:46

sir,

my mistake on myRunnNum table. 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 table

lock mean, there's no duplicate value is generated for trnxNo

I'm stuck, how to generate the runnNum1 in myRunnNum and insert into @tItem(trnxNo) with XML data

My expected result as following,

@tItemidx | TrnxNo   | payer  | amt
-------------------------------
1       10      1       10.00
2       11      2       16.00
3       12      6       35.00
4       13      23      40.00

myRunnNum
idx | code | runnNum1
---------------------------
1     trnxNo | 13

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-31 : 01:47:24

use the same approach as suggested earlier and please dont open multiple threads for same question

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=163706

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources