DECLARE @ThisBSB VarChar(6)
DECLARE @intCount int
DECLARE @intInserted int
DECLARE @intUpdated int

SET @intInserted = 0
SET @intUpdated = 0

-- fields from New Table

DECLARE curBSB CURSOR
FOR
SELECT Replace(bsbnumber,'-','') 
FROM ztblBSBText (nolock)

OPEN curBSB

FETCH NEXT FROM curBSB
INTO @ThisBSB

WHILE @@FETCH_STATUS = 0
BEGIN

    --Print @ThisBSB
    
    -- See if this BSB Already Exists
    SELECT @Intcount = Count(*) 
    FROM tblBankBSB (nolock) 
    WHERE BSBcode = @ThisBSB

    
    IF @intCount = 0 
    BEGIN

	  -- Insert New Record
	  --Print 'Insert: ' + @ThisBSB
	  INSERT INTO tblBankBSB
			  ([BSBCode]
			  ,[BankID]
			  ,[BranchNumber]
			  ,[BranchName]
			  ,[CountryID]
			  ,[Address]
			  ,[Suburb]
			  ,[StateID]
			  ,[StateCode]
			  ,[State]
			  ,[PostcodeID]
			  ,[Postcode]
			  ,[StatusID]
			  ,[TransferedToBSB]
			  ,[CreateID]
			  ,[CreateTS]
			  ,[ModifyID]
			  ,[ModifyTS])
	  SELECT	  @ThisBSB
			  ,tblBank.BankID 
			  ,Cast(Right(bsbnumber,3) AS Int)
			  ,ztblBSBText.BSBName
			  ,1
			  ,ztblBSBText.Address
			  ,ztblBSBText.Suburb
			  ,tblState.StateId
			  ,Null
			  ,ztblBSBText.State
			  ,Null
			  ,ztblBSBText.Postcode
			  ,1
			  ,Null
			  ,Null
			  ,Null
			  ,@UserContactID
			  ,getDate()
	  FROM ztblBSBText
	  INNER JOIN tblBank (nolock)  on ztblBSBText.Mnemonic = tblBank.BankCode
	  INNER JOIN tblState (nolock)  on ztblBSBText.State = tblState.State
	  WHERE tblState.StatusID = 1
	    AND tblState.CountryID = 1			
          AND Replace(bsbnumber,'-','') = @ThisBSB

	  SET @intInserted = @intInserted + 1
    END 

    ELSE 
    BEGIN

	  -- See If Closed since last time this was run, and if so, update
	  SELECT @intCount = Count(*)
	  FROM ztblBSBText
	  INNER JOIN tblBankBSB (nolock)  ON Replace(ztblBSBText.bsbnumber,'-','') = tblBankBSB.BSBCode
	  WHERE Replace(bsbnumber,'-','') = @ThisBSB
	    AND ztblBSBText.BSBName = 'Closed'
	    AND tblBankBSB.BranchName Not Like '%Closed%'

	  IF @intCount > 0 
	  BEGIN 

--		Print 'Update: ' + @ThisBSB
		UPDATE tblBankBSB
		SET  tblBankBSB.StatusID = 0
		    ,tblBankBSB.BranchName = tblBankBSB.BranchName + ' - Closed'
		    ,tblBankBSB.TransferedToBSB = (SELECT replace(substring(address, 14,7),'-','') 
							     FROM ztblBSBText 
							     WHERE Replace(ztblBSBText.bsbnumber,'-','') = @ThisBSB)
			,tblBankBSB.ModifyID = @UserContactID
			,tblBankBSB.ModifyTS = getDate()
		WHERE BSBCode = @ThisBSB

		SET @intUpdated = @intUpdated + 1
	  END

    END 

    FETCH NEXT FROM curBSB
    INTO @ThisBSB

END

CLOSE curBSB
DEALLOCATE curBSB