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.
Best Answer
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
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.
