SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [_2022_ADA_Members] (
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
);

CREATE TABLE [Members] (
Client_Member_ID 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),
STATUS nvarchar(30)
)

INSERT INTO [_2022_ADA_Members]
VALUES
('123', 'John','Smith','123 Address', NULL, 'City', 'State','12345','jsmith@example.com', 'category A',123,NULL,NULL,NULL)
, ('456', 'Roberto','Jones','New Address', NULL, 'City', 'State','12345','rjones@example.com', 'category A',456,NULL,NULL,NULL)
, ('789', 'Mike','Robertson','123 Address', NULL, 'City', 'State','12345','rjones@example.com', 'category A',789,NULL,NULL,NULL)
, ('555', 'Ann','Jones','New Address', NULL, 'City', 'State','12345','rjones@example.com', 'category A',555,NULL,NULL,NULL)
;

INSERT INTO Members (Client_Member_ID, First_Name, Last_Name, Address, Address2, City, State, Zip, Email, Member_Category, STATUS)
VALUES
('456', 'Robert','Jones','123 Address', NULL, 'City', 'State','12345','rjones@example.com', 'category B','Inactive')
, ('555', 'Alice','Jones','123 Address', NULL, 'City', 'State','12345','rjones@example.com', 'category B','Inactive')
;



6 rows affected

UPDATE m 
SET    m.First_name = i.First_name
       , m.Last_name = i.Last_Name 
       , m.Address = i.Address 
       -- , other columns
FROM [_2022_ADA_Members] i INNER JOIN Members m ON i.ADA_NUMBER = m.Client_Member_ID;

SELECT @@ROWCOUNT AS RowsUpdated;

INSERT INTO Members (Client_Member_ID, First_Name, Last_Name, Address, Address2, City, State, Zip, Email, Member_Category, STATUS)
SELECT i.ADA_NUMBER, i.First_Name, i.Last_Name, i.Address, i.Address2, i.City, i.State, i.Zip, i.Email, i.Member_Category, 'Active'
FROM   [_2022_ADA_Members] i LEFT JOIN Members m ON i.ADA_NUMBER = m.Client_Member_ID
WHERE  m.Client_Member_ID IS NULL 

SELECT @@ROWCOUNT AS RowsInserted;



RowsUpdated


2




RowsInserted


2


SELECT * 
FROM   Members


Client_Member_ID
FIRST_NAME
LAST_NAME
ADDRESS
ADDRESS2
CITY
STATE
ZIP
EMAIL
MEMBER_CATEGORY
STATUS


456
Roberto
Jones
New Address
null
City
State
12345
rjones@example.com
category B
Inactive

555
Ann
Jones
New Address
null
City
State
12345
rjones@example.com
category B
Inactive

123
John
Smith
123 Address
null
City
State
12345
jsmith@example.com
category A
Active

789
Mike
Robertson
123 Address
null
City
State
12345
rjones@example.com
category A
Active

Client_Member_ID	FIRST_NAME	LAST_NAME	ADDRESS	ADDRESS2	CITY	STATE	ZIP	EMAIL	MEMBER_CATEGORY	STATUS
456	Roberto	Jones	New Address	null	City	State	12345	rjones@example.com	category B	Inactive
555	Ann	Jones	New Address	null	City	State	12345	rjones@example.com	category B	Inactive
123	John	Smith	123 Address	null	City	State	12345	jsmith@example.com	category A	Active
789	Mike	Robertson	123 Address	null	City	State	12345	rjones@example.com	category A	Active