TSQL - Insert record in a table and increment one column. Data comes from XML.


Today one more task on SQL –
While inserting a record, incremental value of a column, FeeScheduleChargeID to be updated tacitly based on the XML data.
1.       XML data comes from XML datatype
2.       Table has Primary Key, PK which is auto identity column.

Sample

CREATE TABLE [dbo].[SomeTable](
      [PK] [int] IDENTITY(1,1) NOT NULL,
      [FeeScheduleID] [int] NOT NULL,
      [ThisColumnToBeUpdatedID] [int] NULL,
      [ChargeID] [int] NULL,
      [FeeAmount] [money] NULL,
      [sysDateCreated] [datetime] NOT NULL,
      [CreatedByName] [varchar](50) NOT NULL,
      [sysDateLastUpdated] [datetime] NOT NULL,
      [LastUpdateByName] [varchar](50) NULL,
      [ChargeClassification] [int] NULL,
      [FeeType] [bit] NOT NULL,
      [MinimumHours] [real] NULL,
      [Taxable] [bit] NOT NULL,
      [ApplyCharge] [tinyint] NOT NULL,
      [AcctNum] [int] NOT NULL)

-- Insert one record in the above mentioned table.

DECLARE @Xml xml;
SET @Xml ='   
 <Root> 
 <SomeTable> 
 <ChargeID>133</ChargeID> 
 <FeeAmount>123</FeeAmount> 
 <ChargeClassification>82</ChargeClassification> 
 <FeeType>1</FeeType> 
 <MinimumHours>27</MinimumHours> 
 <Taxable>0</Taxable> 
 <ApplyCharge>27</ApplyCharge> 
 <AcctNum>100</AcctNum> 
 </SomeTable> 

<SomeTable> 
 <ChargeID>133</ChargeID> 
<FeeAmount>656</FeeAmount> 
<ChargeClassification>82</ChargeClassification> 
<FeeType>1</FeeType> 
<MinimumHours>27</MinimumHours> 
<Taxable>1</Taxable> 
<ApplyCharge>27</ApplyCharge> 
<AcctNum>100</AcctNum> 
</SomeTable> 
</Root>'

INSERT INTO SomeTable
(ThisColumnToBeUpdatedID , FeeScheduleID ,ChargeID ,
FeeAmount ,sysDateCreated ,CreatedByName , sysDateLastUpdated ,
LastUpdateByName ,ChargeClassification ,FeeType ,MinimumHours ,
Taxable , ApplyCharge ,AcctNum)

SELECT(
        SELECT MAX( ThisColumnToBeUpdatedID) FROM SomeTable
      ) + ROW_NUMBER()OVER( ORDER BY GSQry.ThisColumnToBeUpdatedID ASC ) ThisColumnToBeUpdatedID ,
      GSQry.FeeScheduleID ,
      GSqry.ChargeID ,
      GSqry.FeeAmount ,
      GSqry.sysDateCreated ,
      GSqry.CreatedByName ,
      GSqry.sysDateLastUpdated ,
      GSqry.LastUpdateByName ,
      GSqry.ChargeClassification ,
      GSqry.FeeType ,
      GSqry.MinimumHours ,
      GSqry.Taxable ,
      GSqry.ApplyCharge ,
      GSqry.AcctNum
  FROM(
        SELECT N.a.value( 'ThisColumnToBeUpdatedID[1]' , 'INT')ThisColumnToBeUpdatedID ,
               342 AS FeeScheduleID ,
               N.a.value( 'ChargeID[1]' , 'INT')ChargeID ,
               N.a.value( 'FeeAmount[1]' , 'MONEY')FeeAmount ,
               GETDATE()sysDateCreated ,
               'CreatedByName' CreatedByName ,
               GETDATE()sysDateLastUpdated ,
               'UpdatedByName' LastUpdateByName ,
               N.a.value( 'ChargeClassification[1]' , 'INT') ChargeClassification ,
               N.a.value( 'FeeType[1]' , 'BIT')FeeType ,
               N.a.value( 'MinimumHours[1]' , 'REAL')MinimumHours ,
               N.a.value( 'Taxable[1]' , 'BIT')Taxable ,
               N.a.value( 'ApplyCharge[1]' , 'TINYINT')ApplyCharge ,
               N.a.value( 'AcctNum[1]' , 'INT')AcctNum
          FROM @Xml.nodes( '/Root/SomeTable')AS N( a)
      )GSQry; 

-- Thank You.

Comments

Popular posts from this blog

ASP.NET Compillation Error BC31007 - Solution

The Difference between GET and POST

Test & Debug WCF service using WCFTestClient.exe