eliminate nested while loop

Question

eliminate nested while loop

gan-685682

Ten Centuries

Points: 1216
More actions
July 8, 2008 at 1:24 am

#209658

I have 2 tables(table A with identity column "ID" as PK; table B with column identity ID and column "A_ID" as FK).
I have another XML with:
< root >
< a ID = "1" / >
< b ID = "9" A_ID = "1" / >
< a ID = "2" / >
<b ID = "10" A_ID = "2" / >
< /root >
how can i do batch update
insert into tableA
select ...
by by-passing the nested while-loop
while openxml from tag a
begin
insert into tableA
set temp variable = @@identity
while openxml from tag b
begin
insert into tableB( A_ID)
values(temp variable = @@identity)
end
for getting the correct A_ID is @@identity that ties to column :ID" in table A?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Could you escape the < characters in your xml with & lt; (no space in between) please. In that case we will be able to see your xml document, and find an answer 🙂

- Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 2

this should get you started.

declare @xml xml

set @xml ='<root>

<a ID = "1" />

<b ID = "9" A_ID = "1" />

<a ID = "2" />

<b ID = "10" A_ID = "2" />

</root>'

select data.a.value('@ID','int') as ID from @xml.nodes('//a') as data(a)

select data.b.value('@ID','int') as ID, data.b.value('@A_ID','int') as A_ID from @xml.nodes('//b') as data(b)

gan-685682 Ten Centuries Points: 1216 More actions · Answer 3

The openXML or XQuery I have no problem,

but the column "ID" in table A will be getting from:

set temp variable = @@identity

to insert into column "A_ID" in table B. The xml mapping is only keep for tracing before inserted into table.

thus I must do a while loop or cursor.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

I can't help on the XML stuff... but you may want to consider using SCOPE_IDENTITY() instead... @@IDENTITY will return the wrong number if, for example, you have a trigger on the table that does an audit log insert.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gan-685682 Ten Centuries Points: 1216 More actions · Answer 5

Thanks for your info, though I did not use trigger. Anyone know batch update to eliminate of getting @@scope_identity?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

It's not @@ScopeIdentity... It's SCOPE_IDENTITY().

Now that you put it that way, though, I think you might want to try the new OUTPUT clause in 2k5... that can give you a result set of all the identity values you just created and more. Someone would have to meet your original request though.

Like I said, I can't help in that area... hopefully, someone who can will read this...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gan-685682 Ten Centuries Points: 1216 More actions · Answer 7

since you are talking about identity, I think IDENT_CURRENT('table_name') will also have the scope problem as @@IDENTITY(From BOL: @@IDENTITY is not limited to a specific scope; SCOPE_IDENTITY returns the value only within the current scope; IDENT_CURRENT is not limited by scope and session; it is limited to a specified table), if trigger exists, right?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Not quite... IDENT_CURRENT('table_name') takes a table name as it's operand and has no scope other than the table itself... trigger shouldn't affect it like it can @@IDENTITY.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gan-685682 Ten Centuries Points: 1216 More actions · Answer 9

then you meant ident_current('table name') can be used as scope_identity ?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

gan (7/9/2008)
then you meant ident_current('table name') can be used as scope_identity ?

Absolutely not...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)