Explain File Excel Upload Into SQL

This SQL script defines a stored procedure in RelianceCommissionLiveDB for uploading Excel data into SQL. It parses XML data for validation or updating Upsell Premiums.

File Excel Upload Into SQL

USE [RelianceCommissionLiveDB]
GO
/****** Object:  StoredProcedure [dbo].[USP_UploadUpSellPremium]    Script Date: 27-03-2024 12:02:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------------------------------------------------------
--- Created By : Sagar Jadhav
--- Created On : 14-Mar-2024
--- Purpose : Data Validate & Update UpSell Premium
--- USP_UploadUpSellPremium '<?xml version="1.0" encoding="utf-8" ?><UpSellPremium><Premium><TransactionID>ICM_AUG_18_963589</TransactionID><UpSellPr>15009</UpSellPr></Premium><Premium><TransactionID>ICM_AUG_18_1330170</TransactionID><UpSellPr>18900</UpSellPr></Premium></UpSellPremium>','Admin','VALIDATION'
--- USP_UploadUpSellPremium '<?xml version="1.0" encoding="utf-8" ?><UpSellPremium><Premium><TransactionID>ICM_AUG_18_27499</TransactionID><UpSellPr>15009</UpSellPr></Premium><Premium><TransactionID>ICM_AUG_18_31034</TransactionID><UpSellPr>18900</UpSellPr></Premium></UpSellPremium>','Admin','VALIDATION'
--- USP_UploadUpSellPremium '<?xml version="1.0" encoding="utf-8" ?><UpSellPremium><Premium><TransactionID>ICM_AUG_18_963589</TransactionID><UpSellPr>15009</UpSellPr></Premium><Premium><TransactionID>ICM_AUG_18_1330170</TransactionID><UpSellPr>18900</UpSellPr></Premium></UpSellPremium>','Admin','UPDATE'
--------------------------------------------------------
ALTER Procedure [dbo].[USP_UploadUpSellPremium]
(
@File varchar(MAX),
@UserName varchar(10),
@ActionFlag varchar(20)
)
AS 
BEGIN
        DECLARE @StartDt DATETIME, @EndDt DATETIME
		DECLARE @idoc int                              
		DECLARE @doc xml                              
		SET @doc = @File                              
		EXEC sp_xml_preparedocument @idoc OUTPUT, @doc                               

			SELECT * 
			INTO #Temp_UpSellPr 
			FROM OpenXML(@idoc,'//Premium')                              
			WITH 
			(
			XMLTransactionID VARCHAR(50) 'TransactionID',
			XMLUpSellPr FLOAT 'UpSellPr'
			)

       IF @ActionFlag='VALIDATION'
         BEGIN
		 
			CREATE TABLE #tempError_UpSellPr                                        
			(                                        
				TransactionID VARCHAR(50),
				UpSellPr VARCHAR(10),
				Error VARCHAR(200)
			)

			INSERT INTO #tempError_UpSellPr (TransactionID,UpSellPr,Error)
			SELECT A.XMLTransactionID,A.XMLUpSellPr, 'Invalid TransactionID.' 
			FROM #Temp_UpSellPr A 
			LEFT OUTER JOIN tbl_Premium Pr WITH(NOLOCK) ON A.XMLTransactionID=Pr.TransactionID
			WHERE Pr.TransactionID IS NULL
			
			SELECT TOP 1 @StartDt=StartDate, @EndDt=EndDate FROM tbl_DeviationMaster WHERE IsClosed=0

			INSERT INTO #tempError_UpSellPr (TransactionID,UpSellPr,Error)
			SELECT A.XMLTransactionID,A.XMLUpSellPr, 'This TransactionID is not found in a current processing date.' 
			FROM #Temp_UpSellPr A 
			LEFT OUTER JOIN tbl_Premium Pr WITH(NOLOCK) ON A.XMLTransactionID=Pr.TransactionID
			WHERE Pr.ProcessingDate NOT BETWEEN @StartDt AND @EndDt

			SELECT TransactionID,UpSellPr AS UpSellPremium,Error FROM #tempError_UpSellPr

			DROP TABLE #tempError_UpSellPr
			DROP TABLE #Temp_UpSellPr

		 END

	 IF @ActionFlag='UPDATE'
	      BEGIN
			UPDATE PR SET PR.Upsell_Premium = TempPR.XMLUpSellPr , 
			PR.ITRemark=ISNULL(PR.ITRemark,'..') + ' Previous upsell premium is ' + ISNULL(cast(PR.Upsell_Premium as varchar),'..') + ' & updated by ' + @UserName +''
			FROM tbl_Premium PR WITH(NOLOCK)
			--SELECT PR.TransactionID,TempPR.XMLUpSellPr FROM tbl_Premium PR
			INNER JOIN #Temp_UpSellPr TempPR on TempPR.XMLTransactionID=PR.TransactionID

			SELECT PR.TransactionID,PR.Upsell_Premium,PR.ITRemark FROM tbl_Premium PR WITH(NOLOCK)
			INNER JOIN #Temp_UpSellPr TempPR on TempPR.XMLTransactionID=PR.TransactionID

			DROP TABLE #Temp_UpSellPr
	      END
END

This SQL script defines a stored procedure named [USP_UploadUpSellPremium] within the database RelianceCommissionLiveDB. It begins with setting specific ANSI options and quoted identifier settings.

The procedure takes three parameters: @File, @UserName, and @ActionFlag. The @File parameter expects an XML string containing data for processing.

Upon execution, it parses the XML data and stores it into a temporary table #Temp_UpSellPr. If the @ActionFlag is set to 'VALIDATION', it performs validation checks on the data, such as verifying the existence of TransactionIDs and checking against a specific date range.

If the @ActionFlag is set to 'UPDATE', it updates the Upsell_Premium column in the tbl_Premium table based on the provided data.

Finally, it returns relevant data based on the action flag and drops the temporary tables after use. The script also includes comments indicating its purpose, creation date, and the name of the creator.