USE [FDC]
GO
/** Script Date: 02/01/2021 16:35:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
SET NOCOUNT ON;

DECLARE @ADA_NUMBER nvarchar(255),
        @FIRST_NAME nvarchar(255),
        @LAST_NAME nvarchar(255),
        @ADDRESS nvarchar(255),
        @ADDRESS2 nvarchar(255),
        @CITY nvarchar(255),
        @STATE nvarchar(255),
        @ZIP nvarchar(255),
        @EMAIL nvarchar(255),
        @MEMBER_CATEGORY nvarchar(255),
        @MEMBER_ID INTEGER,
        @ADD_CNT INTEGER,
        @MEM_CNT INTEGER,
        @UPD_CNT INTEGER

DECLARE MEMCUR CURSOR FOR
        SELECT *
        FROM _2022_ADA_Members

SET @ADD_CNT = 0
SET @UPD_CNT = 0

OPEN MEMCUR
FETCH NEXT FROM MEMCUR INTO @ADA_NUMBER, @FIRST_NAME, @LAST_NAME, @ADDRESS, @ADDRESS2, @CITY, @STATE, @ZIP, @EMAIL, @MEMBER_CATEGORY

WHILE @@fetch_status = 0
BEGIN

    SET @MEM_CNT = (SELECT COUNT(*)
                    FROM Members
                    WHERE client_member_id = @ADA_NUMBER)

    IF @MEM_CNT > 0
        BEGIN
            SET @MEMBER_ID = (SELECT TOP 1 Member_ID
                              FROM Members
                              WHERE client_member_id = @ADA_NUMBER
                              ORDER BY Member_ID)

            UPDATE Members
            SET First_Name = @FIRST_NAME,
                Last_Name = @LAST_NAME,
                Address = @ADDRESS,
                Address2 = @ADDRESS2,
                City = @CITY,
                State = @STATE,
                Zip = @ZIP,
                Email = @EMAIL,
                Member_Category = @MEMBER_CATEGORY,
                STATUS = 'Active',
                Deleted = 'No'
            WHERE Member_ID = @MEMBER_ID

            SET @UPD_CNT = @UPD_CNT + 1
            --PRINT @ADA_NUMBER

        END

    IF @MEM_CNT = 0
        BEGIN

            INSERT INTO Members (Client_Member_ID, First_Name, Last_Name, Address, Address2, City, State, Zip, Email, Member_Category, STATUS)
            VALUES (@ADA_NUMBER,
                    @FIRST_NAME,
                    @LAST_NAME,
                    @ADDRESS,
                    @ADDRESS2,
                    @CITY,
                    @STATE,
                    @ZIP,
                    @EMAIL,
                    @MEMBER_CATEGORY,
                    'Active')
            SET @ADD_CNT = @ADD_CNT + 1
        END

    FETCH NEXT FROM MEMCUR INTO @ADA_NUMBER, @FIRST_NAME, @LAST_NAME, @ADDRESS, @ADDRESS2, @CITY, @STATE, @ZIP, @EMAIL, @MEMBER_CATEGORY

END
CLOSE MEMCUR
PRINT @ADD_CNT
PRINT @UPD_CNT
DEALLOCATE MEMCUR
END