Can OPENXML insert rows with some duplicate rows?

8 Dec 2004 1:03 PM

Thana N.

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.

8 Dec 2004 1:27 PM

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.

Show quote

"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.
>

8 Dec 2004 5:21 PM

Michael Rys [MSFT]

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

Show quote

"Thana N." <Tha***@discussions.microsoft.com> wrote in message
news:417C9E34-FF19-4EDB-B57C-345D36770535@microsoft.com...
> 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.
>>

8 Dec 2004 5:06 PM

Bryant

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#