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.