如何在SQL中使用openxml批量插入表时避免重复记录？ [英] How to avoid duplicates records while bulk inserting in to table using openxml in SQL?

问题描述

我希望在SQL中批量插入表时避免重复记录。我传递XML作为输入参数并使用OpenXml插入



我尝试过：

I want to avoid duplicates records while bulk inserting in to Table in SQL. I am passing XML as input parameter and inserting using OpenXml

What I have tried:

EXEC sp_xml_preparedocument @docHandle OUTPUT, @ip_XML                 
BEGIN TRY               
BEGIN TRAN                     
SET @op_error_code = 0;              

IF(@ip_Status ='I')            
BEGIN            

	INSERT INTO ACH_OUTWARD_Files                
	(   
	            
	FileName                 
	, FileType                 
	, Status                 
	, CreatedDate              
	   
	)           
	 
	SELECT indx.FileName                 
	, '102_OUTWARD_ACH'                 
	,  indx.Mode              
	, GETDATE()                 
 
	FROM OPENXML(@docHandle, '/WPS/MDL_ACH_Outward_Details',2)WITH                                  
	(   
	FileName varchar(100)  
	,FileType varchar(50)                 
	, Mode  varchar(1)    
	, CreatedDate DateTime             
	) 

EXEC sp_xml_removedocument @docHandle   

END

推荐答案

为防止插入XML中存在的重复记录，请更改 SELECT 到 SELECT DISTINCT

To prevent inserts of duplicate records that exist in the XML change your SELECT to SELECT DISTINCT

这篇关于如何在SQL中使用openxml批量插入表时避免重复记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！