Thursday, August 16, 2012

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.


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 ='   


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

        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 ,
        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)

-- Thank You.

No comments: