Click here to Skip to main content
14,880,672 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
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
JavaScript
<pre>{"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 I'm trying to insert into the table, I'm checking if the itemcode exists in the table or not and inserting as below

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

What I have tried:

SQL
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)') );
Posted
Updated 24-Mar-21 5:21am
v2

1 solution

Try something like:
SQL
WITH cte As
(
    SELECT
        N.value('(ItemCode/text())[1]', 'varchar(50)') As ItemCode
    FROM
        @ProductDetails.nodes('Root/ProductDetails') As T(N)
)
INSERT INTO inv_productdetails (ItemCode)
SELECT ItemCode
FROM cte As T

EXCEPT

SELECT ItemCode
FROM inv_productdetails;
EXCEPT and INTERSECT (Transact-SQL) - SQL Server | Microsoft Docs[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900