question

iviewtheworldthroughmylens avatar image
iviewtheworldthroughmylens asked

How to avoid duplicates in XML while inserting into SQL Table

Note: this is a sample code and scenario. I will not be using distinct while using select statement.


I'm trying to upload Itemcodes into an empty table for the first time using XML.


below is the XML data


{"ProductDetails":"<Root>\r\n <ProductDetails>\r\n <ItemCode>2503902</ItemCode\r\n </ProductDetails>\r\n <ProductDetails>\r\n <ItemCode>3591948</ItemCode>\r\n </ProductDetails>\r\n

<ProductDetails>\r\n <ItemCode>2503902</ItemCode\r\n </ProductDetails>\r\n</Root>"}



When m trying to insert into the table, I'm checking if the itemcode exists in the table or not and inserting as below


INSERT INTO inv_productdetails(ItemCode)

select T.N.value('(ItemCode/text())[1]', 'varchar(50)')

from @ProductDetails.nodes('Root/ProductDetails') as T(N)

where not exists (select top 1 * from dbo.inv_productdetails

where ItemCode = T.N.value('(ItemCode/text())[1]', 'varchar(50)') );


I'm not using any transaction or anything. Its a plain simple query to insert the itemcodes.

But I have noticed that while the above code runs, its not able to consider the not exists statement.


Can you please help me with a workaround.


Thanks.

t-sqlsqlserversqlserver2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

You need to think about the insert as an atomic operation on a set of data, not row-by-row.

You are selecting all the itemcodes that do not exist in the table - so in the absence of a distinct, will produce 3 values:

  • 2503902
  • 3591948
  • 2503902


1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iviewtheworldthroughmylens avatar image iviewtheworldthroughmylens commented ·

yes Kev Riley...initially i was thinking,the data will be inserted row by row with the XML logic i have written, which would have been easier. I did a work around and checking the duplicates if any before inserting the data and returning message to the front end user.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.