Tuesday, February 14, 2012

Can OPENXML insert rows with some duplicate rows?

Hi All,
I'd like to insert rows that maybe have some duplicate rows with OPENXML
from stored procedure. In normal case, I've found that it cannot do that.
Is there anybody know how to accomplish this?
Thanks in advance,
Thana N.
I've just found that I can use "NOT IN" or "NOT EXISTS" to do it. But I
wondor about the performance of it. What is the best way for performance?
Thana N.
"Thana N." wrote:

> Hi All,
> I'd like to insert rows that maybe have some duplicate rows with OPENXML
> from stored procedure. In normal case, I've found that it cannot do that.
> Is there anybody know how to accomplish this?
> Thanks in advance,
> Thana N.
>
|||In this case you will have to decide how to deal with the duplicates.
This depends a lot on your data. If you were just inserting account
numbers then you could do:
insert into x
select acct#
from openxml(...)
group by acct#
In some cases you may want to sum the data (using sum), get the max, or
get the min. It will depend on your data.
insert into x
select acct#, sum(charges), min(timeleft), max(lastcall)
from openxml(...)
group by acct#
|||If your query would execute OpenXML more than once with the same resulting
rowset or it is large enough to benefit from an index, you may want to
insert the data from OpenXML into a temp table (and define an index if it
seems more performing).
HTH
Michael
"Thana N." <ThanaN@.discussions.microsoft.com> wrote in message
news:417C9E34-FF19-4EDB-B57C-345D36770535@.microsoft.com...[vbcol=seagreen]
> I've just found that I can use "NOT IN" or "NOT EXISTS" to do it. But I
> wondor about the performance of it. What is the best way for performance?
> Thana N.
> "Thana N." wrote:

No comments:

Post a Comment