SQL SERVER, STORED PROCEDURE

I have the below stored procedure where I am doing a commit after every 5000 rows for deletes, updates and inserts but this is not working as I am getting an error - The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Please advise if I need to change my begin statement (begin transaction???) or any other changes that will make my commit work. Thanks.
____________________________________________________________________________________________

USE [EAN]
GO

/****** Object:  StoredProcedure [dbo].[IncrementalOrganization]    Script Date: 03/14/2012 10:11:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IncrementalOrganization]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[IncrementalOrganization]
GO

USE [EAN]
GO

/****** Object:  StoredProcedure [dbo].[IncrementalOrganization]    Script Date: 03/14/2012 10:11:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[IncrementalOrganization]
AS

SET NOCOUNT ON
SET TRAN ISOLATION LEVEL READ UNCOMMITTED

DECLARE
      @RowIDProcedureLog int,
      @RowsInserted bigint,
      @RowsUpdated bigint,
      @RowsDeleted bigint,
      @OrgLastRowID bigint,
      @OrgCurrentRowID bigint,
      @OrgNameLastRowID bigint,
      @OrgNameCurrentRowID bigint,
      @OrgIdentifierLastRowID bigint,
      @OrgIdentifierCurrentRowID bigint,
      @OrgRelationshipLastRowID bigint,
      @OrgRelationshipCurrentRowID bigint,
      --@RDCOrgHorizonLastRowID bigint,
      --@RDCOrgHorizonCurrentRowID bigint,
      @DeleteRowIDMarker int,
      @DeleteRowIDMax int,
      @UpdateRowIDMarker int,
      @UpdateRowIDMax int,
      @InsertRowIDMarker int,
      @InsertRowIDMax int,
      @MaxEANOrganizationID int
      
BEGIN TRY

--Initialize Rowcount variables
SELECT @RowsInserted = 0, @RowsUpdated = 0, @RowsDeleted = 0

INSERT dbo.EANProcedureLog (ProcedureName, UTCStartDateTime, RowsInserted, RowsUpdated, RowsDeleted, StatusDescription) VALUES (OBJECT_NAME(@@PROCID), GETUTCDATE(), 0, 0, 0, 'Started')
SELECT @RowIDProcedureLog = SCOPE_IDENTITY()

   
-- Get Log RowIDs to be used for OAOrganization
SELECT @OrgLastRowID = LastRowID, @OrgCurrentRowID = CurrentRowID
FROM dbo.EANIncrementalState
WHERE SourceTable = 'OAOrganizationLog' and Process = 'IncrementalOrganization'

-- Get Log RowIDs to be used for OAOrganizationName
SELECT @OrgNameLastRowID = LastRowID, @OrgNameCurrentRowID = CurrentRowID
FROM dbo.EANIncrementalState
WHERE SourceTable = 'OAOrganizationNameLog' and Process = 'IncrementalOrganization'

-- Get Log RowIDs to be used for OAIdentifier
SELECT @OrgIdentifierLastRowID = LastRowID, @OrgIdentifierCurrentRowID = CurrentRowID
FROM dbo.EANIncrementalState
WHERE SourceTable = 'OAIdentifierLog' and Process = 'IncrementalOrganization'

-- Get Log RowIDs to be used for OARelationship
SELECT @OrgRelationshipLastRowID = LastRowID, @OrgRelationshipCurrentRowID = CurrentRowID
FROM dbo.EANIncrementalState
WHERE SourceTable = 'OARelationshipLog' and Process = 'IncrementalOrganization'

---- Get Log RowIDs to be used for RDCOrganizationHorizon
--SELECT @RDCOrgHorizonLastRowID = LastRowID, @RDCOrgHorizonCurrentRowID = CurrentRowID
--FROM dbo.EANIncrementalState
--WHERE SourceTable = 'RDCOrganizationHorizonLog' and Process = 'IncrementalOrganization'

CREATE TABLE #log (OrganizationID bigint)

INSERT #log
SELECT OrganizationID
FROM      (
            SELECT OrganizationID
            FROM dbo.OAOrganizationLog l
            WHERE l.RowID > @OrgLastRowID
            AND l.RowID <=  @OrgCurrentRowID
                  UNION
            SELECT OrganizationID
            FROM dbo.OAOrganizationNameLog l
            WHERE l.RowID > @OrgNameLastRowID
            AND l.RowID <=  @OrgNameCurrentRowID
                  UNION
            SELECT EntityID
            FROM dbo.OAIdentifierLog l
            WHERE l.RowID > @OrgIdentifierLastRowID
            AND l.RowID <=  @OrgIdentifierCurrentRowID      
                  UNION
            SELECT RelatedFromEntityID
            FROM dbo.OARelationshipLog l
            WHERE l.RowID > @OrgRelationshipLastRowID
            AND l.RowID <=  @OrgRelationshipCurrentRowID
            AND RelatedFromEntityType = 'Organization'
                  UNION
            SELECT RelatedFromEntityID
            FROM dbo.OARelationshipLog l
            WHERE l.RowID > @OrgRelationshipLastRowID
            AND l.RowID <=  @OrgRelationshipCurrentRowID
            AND RelatedFromEntityType = 'Organization'
            ) T

CREATE TABLE #EANOrganization (
      [OrganizationID] [bigint] NULL,
      [NDAOrgID] [int] NULL,
      [OrganizationPI] [bigint] NULL,
      [NDAOrganizationType] [varchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrganizationTypeCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrganizationSubTypeCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrganizationProviderTypeCode] [tinyint] NULL,
      [OrganizationJurisdictionOfIncorporationTypeCode] [int] NULL,
      [CommonName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CommonNameLanguageID] [bigint] NULL,
      [DomiciledInGeographyID] [bigint] NULL,
      [IncorporatedInGeographyID] [bigint] NULL,
      [ImmediateParentEANOrgID] [int] NULL,
      [UltimateParentEANOrgID] [int] NULL,
      [SucceededByEANOrgID] [int] NULL,
      [UltimatelySucceededByEANOrgID] [int] NULL,
      [ImmediateParentOrganizationID] [bigint] NULL,
      [UltimateParentOrganizationID] [bigint] NULL,
      [SucceededByOrganizationID] [bigint] NULL,
      [UltimatelySucceededByOrganizationID] [bigint] NULL,
      [EdCoID] [varchar](30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
      [EdCoIDEANQuoteID] [int] NULL,
      [EdCoIDQuoteID] [bigint] NULL,
      [PrimaryEANReportingEntityID] [int] NULL,
      [PrimaryReportingEntityCode] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PrimaryEANInstrumentID] [int] NULL,
      [PrimaryInstrumentID] [bigint] NULL,
      PrimaryInstrumentPI bigint NULL,
      [ValuationEANQuoteID] [int] NULL,
      [PrimaryTradedEANQuoteID] [int] NULL,
      [ValuationQuoteID] [bigint] NULL,
      [PrimaryTradedQuoteID] [bigint] NULL,
      [OrganizationWebsite] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [RoleToIssueStatus] [varchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrganizationStatusCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [IsPublic] [bit] NULL,
      [IPODate] [datetime] NULL,
      [OrganizationInactiveEventTypeCode] [tinyint] NULL,
      [OrganizationInactiveYear] [smallint] NULL,
      [OrganizationInactiveMonth] [tinyint] NULL,
      [OrganizationInactiveDay] [tinyint] NULL,
      [OrganizationFoundedYear] [smallint] NULL,
      [OrganizationFoundedMonth] [tinyint] NULL,
      [OrganizationFoundedDay] [tinyint] NULL,
      [IsOrganizationManaged] [bit] NULL,
      [IsOrganizationVerified] [bit] NULL,
      [BusinessIntelligenceExists] [varchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OfficersDirectorsExist] [varchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SignificantDevelopmentsExist] [varchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DocumentsExist] [varchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [FilingsExist] [varchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [HasDeals] [bit] NULL,
      [HasOwnership] [bit] NULL,
      [IsIndexConstituent] [bit] NULL,
      [IsFundSupportCompany] [varchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CIK] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DisclosureID] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [EedbID] [bigint] NULL,
      [LipperID] [bigint] NULL,
      [MXID] [int] NULL,
      [SDCCusip] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SDCID] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TMTCompanyID] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [VEFirmID] [bigint] NULL,
      [VentureEconomicsID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TRBCPrimaryIndustryID] [bigint] NULL,
      [EntityLastReviewedDate] [datetime] NULL
)

CREATE CLUSTERED INDEX ix_#EANOrganization ON #EANOrganization(OrganizationID)

CREATE INDEX ix_#EANOrganization_OrgPI ON #EANOrganization(OrganizationPI)

INSERT #EANOrganization (
      OrganizationId, OrganizationWebsite, OrganizationTypeCode, OrganizationSubTypeCode, OrganizationStatusCode,
      IsPublic, IpoDate, OrganizationInactiveYear, OrganizationInactiveMonth, OrganizationInactiveDay,
      OrganizationFoundedYear, OrganizationFoundedMonth, OrganizationFoundedDay, OrganizationProviderTypeCode,
      OrganizationJurisdictionOfIncorporationTypeCode, OrganizationInactiveEventTypeCode,
      IsOrganizationManaged, IsOrganizationVerified, EntityLastReviewedDate )
SELECT
      o.OrganizationId, OrganizationWebsite, OrganizationTypeCode, OrganizationSubTypeCode, OrganizationStatusCode,
      IsPublicFlag as IsPublic, IpoDate , OrganizationInactiveYear, OrganizationInactiveMonth, OrganizationInactiveDay,
      OrganizationFoundedYear, OrganizationFoundedMonth, OrganizationFoundedDay, OrganizationProviderTypeCode,
      OrganizationJurisdictionOfIncorporationTypeCode, OrganizationInactiveEventTypeCode,
      IsOrganizationManaged, IsOrganizationVerified, EntityLastReviewedDate  
FROM #log l
JOIN dbo.OAOrganization o on (l.OrganizationID = o.OrganizationID)


/*
Update of IDs fails because of bad data in staging. Run this if you need to get rid of overflows
begin tran            
DELETE
FROM Identifier
where SymbolTypeCode = 'NDAOrgID'
and cast(SymbolValue as bigint) > 2147483647      
--commit
*/


UPDATE eo
SET
      OrganizationPI = Ident.OrgPI,
      MXID = Ident.MXID,
      NDAOrgID = Ident.NDAOrgID,
      TmtCompanyId = Ident.TmtCompanyId,
      SDCCUSIP = Ident.SDCCUSIP,
      SdcId = Ident.SdcId,
      DisclosureId = Ident.DisclosureId,
      CIK = Ident.Cik,
      VentureEconomicsId = Ident.VentureEconomicsId,
      Edcoid = Ident.Edcoid,
      EEDBID = Ident.EEDBID,
      LipperID = Ident.LipperID,
      VEFirmID = Ident.VEFirmID
FROM dbo.#EANOrganization eo
JOIN      (
            SELECT EntityId,
            MAX(CASE SymbolTypeCode WHEN 'PI' THEN SymbolValue ELSE NULL END) as OrgPI,
            MAX(CASE SymbolTypeCode WHEN 'MXID' THEN SymbolValue ELSE NULL END) as MXID,
            MAX(CASE SymbolTypeCode WHEN 'NDAOrgID' THEN SymbolValue ELSE NULL END) as NDAOrgID,
            MAX(CASE SymbolTypeCode WHEN 'TmtCompanyId' THEN SymbolValue ELSE NULL END) as TmtCompanyId,
            MAX(CASE SymbolTypeCode WHEN 'SDCCUSIP' THEN SymbolValue ELSE NULL END) as SDCCUSIP,
            MAX(CASE SymbolTypeCode WHEN 'SdcId' THEN SymbolValue ELSE NULL END) as SdcId,
            MAX(CASE SymbolTypeCode WHEN 'DisclosureId' THEN SymbolValue ELSE NULL END) as DisclosureId,
            MAX(CASE SymbolTypeCode WHEN 'Cik' THEN SymbolValue ELSE NULL END) as Cik,
            MAX(CASE SymbolTypeCode WHEN 'VentureEconomicsId' THEN SymbolValue ELSE NULL END) as VentureEconomicsId,
            MAX(CASE SymbolTypeCode WHEN 'Edcoid' THEN SymbolValue ELSE NULL END) as Edcoid,
            MAX(CASE SymbolTypeCode WHEN 'EEDBID' THEN SymbolValue ELSE NULL END) as EEDBID,
            MAX(CASE SymbolTypeCode WHEN 'LipperID' THEN SymbolValue ELSE NULL END) as LipperID,
            MAX(CASE SymbolTypeCode WHEN 'VEFirmID' THEN SymbolValue ELSE NULL END) as VEFirmID
      FROM
      (
            SELECT EntityID, SymbolValue, SymbolTypeCode,--CAST(SymbolValue as bigint) as OrganizationPI,
             ROW_NUMBER() OVER (PARTITION BY EntityID, SymbolTypeCode ORDER BY EffectiveFrom DESC) as IdentifierSequence
            FROM #log l
            JOIN dbo.OAIdentifier o on (l.OrganizationID = o.EntityID)
            WHERE SymbolTypeCode IN ('PI', 'MXID', 'NDAOrgID', 'TmtCompanyId', 'SDCCUSIP', 'SdcId', 'DisclosureId', 'Cik', 'VentureEconomicsId', 'Edcoid', 'EEDBID', 'LipperID', 'VEFirmID')
            AND EffectiveTo IS NULL
            AND IsPrimary = 1
            ) T
      WHERE IdentifierSequence = 1
      GROUP BY EntityId
            ) Ident on (eo.OrganizationID = Ident.EntityId)

-- See if any Orgs coming from RDC can now be transferred to coming from OA
-- Any org without an OrganizationID is coming from RDC. If the OrganizationPI
-- for any of these is now found in OA, set the value of the OrganizationID and
-- that record will now be updated from OA.

UPDATE eo
SET
      OrganizationID = o.OrganizationID
FROM dbo.EANOrganization eo
JOIN #EANOrganization o on (eo.OrganizationPI = o.OrganizationPI)
WHERE eo.OrganizationID IS NULL

SELECT @RowsUpdated = @@ROWCOUNT


UPDATE eo
SET
      CommonName = COALESCE(InterimResult, OrganizationName),
      CommonNameLanguageID = CASE WHEN InterimResult IS NULL THEN LanguageId ELSE 505062 END
FROM #EANOrganization eo
JOIN      (
            SELECT
            n.OrganizationId,
            ROW_NUMBER() OVER (PARTITION BY n.OrganizationID ORDER BY
                  CASE LanguageID WHEN 505062 THEN '0'
                  ELSE '1'
                  END, EffectiveFrom DESC, OrganizationName) as NameSequence,
            COALESCE(
                        CASE LanguageID
                              WHEN 505062 THEN OrganizationName
                              ELSE NULL
                        END,
                        OrganizationNameEnglish, OrganizationNameEnglishNormalized, OrganizationNameEnglish, OrganizationNameTransliterated) as InterimResult,
            OrganizationName,
            LanguageId      
            FROM #log l
            JOIN dbo.OAOrganizationName n on (l.OrganizationID = n.OrganizationID)
            WHERE OrganizationNameTypeCode = 'LNG'
            AND EffectiveTo is null
            ) T ON (eo.OrganizationID = T.OrganizationId)
WHERE NameSequence = 1


UPDATE eo
SET
      DomiciledInGeographyID = Rel.IsDomiciledIn,
      IncorporatedInGeographyID = Rel.IsIncorporatedIn,
      SucceededByOrganizationID = Rel.IsSucceededBy,
      SucceededByEANOrgID = succ.EANOrgID
FROM #EANOrganization eo
JOIN      (
            SELECT
                  RelatedFromEntityID,
                  MAX(CASE RelationshipTypeCode WHEN 'IsDomiciledIn' THEN RelatedToEntityID ELSE NULL END) as IsDomiciledIn,
                  MAX(CASE RelationshipTypeCode WHEN 'IsIncorporatedIn' THEN RelatedToEntityID ELSE NULL END) as IsIncorporatedIn,
                  MAX(CASE RelationshipTypeCode WHEN 'IsSucceededBy' THEN RelatedToEntityID ELSE NULL END) as IsSucceededBy
            FROM (
                  SELECT RelatedFromEntityID, RelationshipTypeCode, MAX(RelatedToEntityID) as RelatedToEntityID --MAX required because of data errors (Duplicates)
                  FROM #log l
                  JOIN OARelationship r on (l.OrganizationID = r.RelatedFromEntityID)
                  WHERE RelationshipTypeCode in ('IsIncorporatedIn', 'IsDomiciledIn', 'IsSucceededBy')
                  AND EffectiveTo IS NULL
                  AND RelatedFromEntityID <> RelatedToEntityID -- prevents errors where Org is succeeded by itself
                  GROUP BY RelatedFromEntityID, RelationshipTypeCode
                   ) T
            GROUP BY RelatedFromEntityID
            ) Rel on (eo.OrganizationID = Rel.RelatedFromEntityId)
LEFT JOIN dbo.EANOrganization succ on (IsSucceededBy = succ.OrganizationID)

SELECT RelatedToEntityID as ParentOrg, 'P' as ParentType, MAX(RelatedFromEntityID) as SubsidOrg
INTO #parent
--FROM #log l
--JOIN dbo.OARelationship r on (l.OrganizationID = r.Re
FROM dbo.OARelationship
WHERE RelationshipTypeCode = 'IsParentOf'
AND EffectiveTo IS NULL
GROUP BY RelatedToEntityID
HAVING COUNT(*) = 1

INSERT #parent
SELECT *
FROM      (
            SELECT RelatedFromEntityID as ParentOrg, 'A' as ParentType, MAX(RelatedToEntityID) as SubsidOrg
            FROM dbo.OARelationship
            WHERE RelationshipTypeCode = 'IsAffiliatedWith'
            AND EffectiveTo IS NULL
            GROUP BY RelatedFromEntityID
            HAVING COUNT(*) = 1
            ) affil
WHERE NOT EXISTS (SELECT 1 from #parent p WHERE p.ParentOrg = affil.ParentOrg and p.SubsidOrg = affil.SubsidOrg)

UPDATE eo
SET
      ImmediateParentOrganizationID = p.ParentOrg
FROM #EANOrganization eo
JOIN #parent p on (p.SubsidOrg = eo.OrganizationID)


--SELECT @RowsUpdated = @RowsUpdated + @@ROWCOUNT

-- Use ultimate parent "as is". Alternative idea (not coded): walk the hierarchy of Parent/Affiliate data, but last hop must
-- be a Parent relationship e.g. A1->A2->P3->A4->P5 is fine. P1->A2->P3->A4->A5 is not and would become: P1->A2->P3

UPDATE eo
SET
      UltimateParentOrganizationID = UltP.ParentOrg
FROM #EANOrganization eo
JOIN      (
            SELECT RelatedToEntityID as ParentOrg, MAX(RelatedFromEntityID) as SubsidOrg
            FROM dbo.OARelationship
            WHERE RelationshipTypeCode = 'IsUltimateParentOf'
            AND EffectiveTo IS NULL
            GROUP BY RelatedToEntityID
            HAVING COUNT(*) = 1
            ) as UltP on (UltP.SubsidOrg = eo.OrganizationID)
JOIN dbo.EANOrganization po on (UltP.ParentOrg = po.OrganizationID)

-- Process DELETEs
-- These are any records where OrganizationID is in the #Log but not in #EANOrganization

SELECT
ROW_NUMBER() OVER (ORDER BY l.OrganizationID) as RowID,
OrganizationID
INTO #ToBeDeleted
FROM #log l
WHERE NOT EXISTS (SELECT 1 FROM #EANOrganization eo WHERE l.OrganizationID = eo.OrganizationID)


--Get MAX number of RowIDs to be DELETEd, and also initialize the Marker to 1
SELECT @DeleteRowIDMarker = 1, @DeleteRowIDMax = COALESCE(MAX(RowID), 0)
FROM #ToBeDeleted

WHILE @DeleteRowIDMarker <= @DeleteRowIDMax
BEGIN
      -- Perform DELETE, 5000 Rows at a time
      DELETE ei
      FROM dbo.EANOrganization ei
      JOIN #ToBeDeleted d on (ei.OrganizationID = d.OrganizationID)
      WHERE d.RowID >= @DeleteRowIDMarker
      AND d.RowID < @DeleteRowIDMarker + 5000
     
      SELECT @RowsDeleted = @RowsDeleted + @@ROWCOUNT
     
      SELECT @DeleteRowIDMarker = @DeleteRowIDMarker + 5000
     
      COMMIT
     
END

-- Process UPDATEs
-- These are any records where OrganizationID is in the #Log and also in #EANOrganization

SELECT
ROW_NUMBER() OVER (ORDER BY l.OrganizationID) as RowID,
OrganizationID
INTO #ToBeUpdated
FROM #log l
WHERE EXISTS (SELECT 1 FROM #EANOrganization ei WHERE l.OrganizationID = ei.OrganizationID)


--Get MAX number of RowIDs to be UPDATEd, and also initialize the Marker to 1
SELECT @UpdateRowIDMarker = 1, @UpdateRowIDMax = COALESCE(MAX(RowID), 0)
FROM #ToBeUpdated


WHILE @UpdateRowIDMarker <= @UpdateRowIDMax
BEGIN
      -- Perform UPDATE
      UPDATE eo
      SET
            OrganizationId = t.OrganizationId,
            OrganizationWebsite = t.OrganizationWebsite,
            OrganizationTypeCode = t.OrganizationTypeCode,
            OrganizationSubTypeCode = t.OrganizationSubTypeCode,
            OrganizationStatusCode = t.OrganizationStatusCode,
            IsPublic = t.IsPublic,
            IpoDate = t.IpoDate,
            OrganizationInactiveYear = t.OrganizationInactiveYear,
            OrganizationInactiveMonth = t.OrganizationInactiveMonth,
            OrganizationInactiveDay = t.OrganizationInactiveDay,
            OrganizationFoundedYear = t.OrganizationFoundedYear,
            OrganizationFoundedMonth = t.OrganizationFoundedMonth,
            OrganizationFoundedDay = t.OrganizationFoundedDay,
            OrganizationProviderTypeCode = t.OrganizationProviderTypeCode,
            OrganizationJurisdictionOfIncorporationTypeCode = t.OrganizationJurisdictionOfIncorporationTypeCode,
            OrganizationInactiveEventTypeCode = t.OrganizationInactiveEventTypeCode,
            IsOrganizationManaged = t.IsOrganizationManaged,
            IsOrganizationVerified = t.IsOrganizationVerified,
            EntityLastReviewedDate = t.EntityLastReviewedDate,
            OrganizationPI  = t.OrganizationPI ,
            MXID  = t.MXID ,
            NDAOrgID  = t.NDAOrgID ,
            TmtCompanyId  = t.TmtCompanyId ,
            SDCCUSIP  = t.SDCCUSIP ,
            SdcId  = t.SdcId ,
            DisclosureId  = t.DisclosureId ,
            CIK  = t.CIK ,
            VentureEconomicsId  = t.VentureEconomicsId ,
            Edcoid = t.Edcoid,
            EEDBID = t.EEDBID,
            LipperID  = t.LipperID ,
            VEFirmID  = t.VEFirmID ,
            CommonName  = t.CommonName ,
            CommonNameLanguageID = t.CommonNameLanguageID,
            ImmediateParentOrganizationID  = t.ImmediateParentOrganizationID ,
            UltimateParentOrganizationID  = t.UltimateParentOrganizationID ,
            DomiciledInGeographyID = t.DomiciledInGeographyID,
            IncorporatedInGeographyID = t.IncorporatedInGeographyID,
            SucceededByOrganizationID = t.SucceededByOrganizationID,
            SucceededByEANOrgID = t.SucceededByEANOrgID
      FROM dbo.EANOrganization eo
      JOIN #EANOrganization t ON (eo.OrganizationID = t.OrganizationID)
      JOIN #ToBeUpdated u on (u.OrganizationID = t.OrganizationID)
      WHERE u.RowID >= @UpdateRowIDMarker
      AND u.RowID < @UpdateRowIDMarker + 5000

      SELECT @RowsUpdated = @RowsUpdated + @@ROWCOUNT
     
      SELECT @UpdateRowIDMarker = @UpdateRowIDMarker + 5000
      
      COMMIT
      
END

-- Process INSERTs
-- These are any records where InstrumentPI is in #EANIndex but not in EANInstrument

SELECT
ROW_NUMBER() OVER (ORDER BY eo.OrganizationID) as RowID,
eo.OrganizationID
INTO #ToBeInserted
FROM #EANOrganization eo
WHERE NOT EXISTS (SELECT 1 FROM dbo.EANOrganization l WHERE l.OrganizationID = eo.OrganizationID)


--Get MAX number of RowIDs to be INSERTed, and also initialize the Marker to 1
SELECT @InsertRowIDMarker = 1, @InsertRowIDMax = COALESCE(MAX(RowID), 0)
FROM #ToBeInserted


WHILE @InsertRowIDMarker <= @InsertRowIDMax
BEGIN
      -- Perform INSERT

      -- Get the max(EANInstrumentID)
      SELECT @MaxEANOrganizationID = MAX(EANOrgID)
      FROM dbo.EANOrganization
     
      INSERT dbo.EANOrganization (
            EANOrgID, OrganizationID, OrganizationWebsite, OrganizationTypeCode, OrganizationSubTypeCode,
            OrganizationStatusCode, IsPublic, IpoDate, OrganizationInactiveYear, OrganizationInactiveMonth,
            OrganizationInactiveDay, OrganizationFoundedYear, OrganizationFoundedMonth, OrganizationFoundedDay,
            OrganizationProviderTypeCode, OrganizationJurisdictionOfIncorporationTypeCode,
            OrganizationInactiveEventTypeCode, IsOrganizationManaged, IsOrganizationVerified,
            EntityLastReviewedDate, OrganizationPI , MXID , NDAOrgID , TmtCompanyId , SDCCUSIP , SdcId ,
            DisclosureId , CIK , VentureEconomicsId , Edcoid, EEDBID, LipperID , VEFirmID , CommonName ,
            CommonNameLanguageID, ImmediateParentOrganizationID , UltimateParentOrganizationID ,
            DomiciledInGeographyID, IncorporatedInGeographyID, SucceededByOrganizationID, SucceededByEANOrgID )
      SELECT
            @MaxEANOrganizationID + i.RowID, t.OrganizationID, OrganizationWebsite, OrganizationTypeCode, OrganizationSubTypeCode,
            OrganizationStatusCode, IsPublic, IpoDate, OrganizationInactiveYear, OrganizationInactiveMonth,
            OrganizationInactiveDay, OrganizationFoundedYear, OrganizationFoundedMonth, OrganizationFoundedDay,
            OrganizationProviderTypeCode, OrganizationJurisdictionOfIncorporationTypeCode,
            OrganizationInactiveEventTypeCode, IsOrganizationManaged, IsOrganizationVerified,
            EntityLastReviewedDate, OrganizationPI , MXID , NDAOrgID , TmtCompanyId , SDCCUSIP , SdcId ,
            DisclosureId , CIK , VentureEconomicsId , Edcoid, EEDBID, LipperID , VEFirmID , CommonName ,
            CommonNameLanguageID, ImmediateParentOrganizationID , UltimateParentOrganizationID ,
            DomiciledInGeographyID, IncorporatedInGeographyID, SucceededByOrganizationID, SucceededByEANOrgID
      FROM #EANOrganization t
      JOIN #ToBeInserted i on (i.OrganizationID = t.OrganizationID)
      WHERE i.RowID >= @InsertRowIDMarker
      AND i.RowID < @InsertRowIDMarker + 5000

      SELECT @RowsInserted = @RowsInserted + @@ROWCOUNT

      SELECT @InsertRowIDMarker = @InsertRowIDMarker + 5000
      
      COMMIT
      
END

-- UPDATE EANIncrementalState
UPDATE dbo.EANIncrementalState
SET LastRowID = @OrgCurrentRowID
WHERE SourceTable = 'OAOrganizationLog' and Process = 'IncrementalOrganization'

UPDATE dbo.EANIncrementalState
SET LastRowID = @OrgNameCurrentRowID
WHERE SourceTable = 'OAOrganizationNameLog' and Process = 'IncrementalOrganization'

UPDATE dbo.EANIncrementalState
SET LastRowID = @OrgIdentifierCurrentRowID
WHERE SourceTable = 'OAIdentifierLog' and Process = 'IncrementalOrganization'

UPDATE dbo.EANIncrementalState
SET LastRowID = @OrgRelationshipCurrentRowID
WHERE SourceTable = 'OARelationshipLog' and Process = 'IncrementalOrganization'

--UPDATE dbo.EANIncrementalState
--SET LastRowID = @RDCOrgHorizonCurrentRowID
--WHERE SourceTable = 'RDCOrganizationHorizonLog' and Process = 'IncrementalOrganization'


UPDATE dbo.EANProcedureLog
SET
      UTCEndDateTime = GETUTCDATE(),
      RowsInserted = @RowsInserted,
      RowsUpdated = @RowsUpdated,
      RowsDeleted = @RowsDeleted,
      StatusDescription = 'Success'
WHERE RowID = @RowIDProcedureLog



END TRY


BEGIN CATCH

     
DECLARE @ErrorID VARCHAR(10);  

-- EXEC dbo.EANLogSqlExceptions @ErrorID OUTPUT
EXEC dbo.EANLogSqlExceptions @EANLogSqlExceptionsID = @ErrorID OUTPUT

RAISERROR('ERROR: Error in Stored Proc. Please check the EANSqlExceptionLogID table for the EANLogSqlExceptionsID = %s.', 16,1, @ErrorID )


END CATCH









GO