Reference

Table ListMembers

Description

This table supports bulk operations. For bulk Add/Update use UPSERT operation. All other operations are row by row.

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.
Method Supported Reference EndPoint
SELECT get_list_members
INSERT add_list_member
UPDATE update_list_member
UPSERT upsert_list_members
DELETE delete_list_member
LOOKUP get_list_member

Examples

SSIS

Use Mailchimp Connector in API Source component to read data or in API Destination component to read/write data:

Read from ListMembers table using API Source

API Source - Mailchimp
Read and write Mailchimp data effortlessly. Integrate, manage, and automate campaigns, lists, members, and reports — almost no coding required.
Mailchimp
ListMembers
Required Parameters
List Id Fill-in the parameter...
Optional Parameters
Email Type
Status
Since Timestamp Opt (Format: 2015-10-21T15:41:36)
Before Timestamp Opt (Format: 2015-10-21T15:41:36)
Since Last Changed (Format: 2015-10-21T15:41:36)
Before Last Changed (Format: 2015-10-21T15:41:36)
Unique Email Id
VIP Only
Interest Category Id
Interest Ids
Interest Match
Since Last Campaign
Unsubscribed Since (Format: 2015-10-21T15:41:36)
SSIS API Source - Read from table or endpoint

Read/write to ListMembers table using API Destination

API Destination - Mailchimp
Read and write Mailchimp data effortlessly. Integrate, manage, and automate campaigns, lists, members, and reports — almost no coding required.
Mailchimp
ListMembers
Select
Required Parameters
List Id Fill-in the parameter...
Optional Parameters
Email Type
Status
Since Timestamp Opt (Format: 2015-10-21T15:41:36)
Before Timestamp Opt (Format: 2015-10-21T15:41:36)
Since Last Changed (Format: 2015-10-21T15:41:36)
Before Last Changed (Format: 2015-10-21T15:41:36)
Unique Email Id
VIP Only
Interest Category Id
Interest Ids
Interest Match
Since Last Campaign
Unsubscribed Since (Format: 2015-10-21T15:41:36)
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read a list member by ID

<p>Gets a single member by subscriber hash ID. Provide <code>ListId</code> in the <code>WITH</code> clause and <code>Id</code> in <code>WHERE</code> or <code>WITH</code>.</p> 

SELECT * FROM ListMembers 
WHERE Id='170a0722daae03855d6434eb3a5959fb' 
WITH (ListId='e246f7e24d')

Read a list member by email

<p>Gets a single member by email address. Pass <code>ListId</code> and <code>Id</code> (email) in the <code>WITH</code> clause.</p> 

SELECT * FROM ListMembers 
WITH (ListId='e246f7e24d', Id='test@abc.com')

Delete list member (archive)

<p>Archives a list member by ID using <code>DELETE FROM ListMembers WHERE Id='...' WITH (ListId='...')</code>. Archived or bounced records cannot be resubscribed; to resubscribe, use the permanent delete operation first to remove the member permanently.</p> 

DELETE FROM ListMembers 
WHERE Id='170a0722daae03855d6434eb3a5959fb' 
WITH (ListId='e246f7e24d')

Delete list members (archive) by date

<p>Archives list members that opted in after a specific date using <code>DELETE FROM ListMembers WHERE TimestampOpt > '...' WITH (ListId='...')</code>. For permanent delete, use the permanent delete operation instead.</p> 

DELETE from ListMembers 
WHERE TimestampOpt > '2023-06-16' 
WITH (ListId='a4d24015f8')

Read members across all lists

<p>Gets members from all lists by querying <code>ListMembers</code> without <code>ListId</code>. The query scans all lists and fetches members for each; this may return a large result set.</p> 

SELECT * FROM ListMembers --scan all lists and then fetch members for each list

Read a member by ID across all lists

<p>Gets a specific member by ID (subscriber hash) across all lists. Use <code>WHERE Id='...'</code> and omit <code>ListId</code>.</p> 

SELECT * FROM ListMembers 
WHERE Id='170a0722daae03855d6434eb3a5959fb'

Read members in a list (selected columns)

<p>Gets only the columns you specify for members in a list. Pass <code>ListId</code> in the <code>WITH</code> clause and list desired columns in the <code>SELECT</code> clause.</p> 

SELECT Id, ListId, EmailAddress, UniqueEmailId, ContactId, FullName, WebId, EmailType, Status, ConsentsToOneToOneMessaging, FirstName, LastName, AddressLine1, AddressLine2, City, State, Zip, Country, Phone, Birthday, StatsAvgOpenRate, StatsAvgClickRate, IpSignup, TimestampSignup, IpOpt, TimestampOpt, MemberRating, LastChanged, Language, Vip, EmailClient, Latitude, Longitude, LocationGmtOff, LocationDstOff, CountryCode, TimeZone, Region, Source, TagsCount, Tags 
FROM ListMembers 
WITH (ListId='e246f7e24d')

Create list member

<p>Adds a new member to a list. Required columns include <code>EmailAddress</code> and <code>Status</code> (e.g. subscribed, pending). Pass <code>ListId</code> in the <code>WITH</code> clause. Optional: <code>MergeFields</code>, <code>Tags</code>, <code>Language</code>, <code>Vip</code>, signup/opt timestamps.</p> 

INSERT INTO ListMembers
(EmailAddress, Status, EmailType, MergeFields, Language, Vip, Latitude, Longitude, IpSignup, TimestampSignup, IpOpt, TimestampOpt, Tags)
VALUES
('np-brucewayne1@zappysys.com', 'subscribed', 'html',
'{"FNAME":"John","LNAME":"Doe","ADDRESS":{"addr1":"123","Freddie":"Ave","city":"Atlanta","state":"GA","zip":"12345"}}',
'en', false, '41.881832', '-87.623177', '192.168.0.8', '2023-04-01 18:00:00', '192.168.0.8', '2023-04-02 14:00:00',
'["Newtag1","Newtag2","Newtag3"]')
WITH (ListId='a4d24015f8')

Update list member

<p>Updates an existing list member. Use <code>UPDATE ListMembers SET ... WHERE Id='...' WITH (ListId='...')</code>. You can change <code>EmailAddress</code>, <code>Status</code>, <code>EmailType</code>, merge fields, <code>Tags</code>, and other profile fields.</p> 

UPDATE ListMembers 
SET EmailAddress='brucewayne10@mycompany.com'
, Status='subscribed' --subscribed, unsubscribed, cleaned, pending
, EmailType='text' --html, text
, Vip='false' --true
, FirstName='Bruce'
, LastName='Wayne'
/*,MergeFields= '{
        "FNAME": "Bruce1",
        "LNAME": "Wayne1",
        "PHONE": "678-111-1234"
    }',
*/
, Language='en' --fr
, TimestampOpt='2023-04-02 11:37:49' 
, Latitude='38.8951' , Longitude='-77.0364' --Washington DC
, Tags='["tag1","tag2"]'
WHERE Id='e9f73ced3b649f0ca829103bcacb2846' 
WITH (ListId='a4d24015f8',SkipMergeValidation='false')

Bulk upsert members from SQL Server

<p>Subscribes or unsubscribes members in bulk using <code>UPSERT INTO ListMembers SOURCE('MSSQL', ...)</code>. Data is read from the SQL Server database; result set column names (or aliases) must match <code>ListMembers</code> input columns (e.g. <code>EmailAddress</code>, <code>FirstName</code>, <code>Status</code>). Pass <code>ListId</code> in the <code>WITH</code> clause.</p> 

UPSERT INTO ListMembers
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
,'select ''brucewayne10@gmail.com'' EmailAddress,''first1'' as FirstName, ''subscribed'' Status 
UNION ALL
select ''test55@gmail.com'' EmailAddress,''first1'' FirstName, ''subscribed'' Status 
')
WITH(ListId='a4d24015f8')

 --//column name alias must match with InputColumns of ListMembers

Bulk upsert members from CSV

<p>Subscribes or unsubscribes members from a CSV file using <code>UPSERT INTO ListMembers SOURCE('ODBC', ...)</code> with the ZappySys CSV Driver. Map CSV columns to <code>ListMembers</code> input columns (e.g. <code>col1 as EmailAddress</code>, <code>col2 as FirstName</code>, <code>col3 as Status</code>). Pass <code>ListId</code> in the <code>WITH</code> clause.</p> 

UPSERT INTO ListMembers
SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=c:\subscribers.csv'
,'select col1 as EmailAddress,col2 as FirstName, col3 as Status from $') --//column name alias must match with InputColumns of ListMembers
WITH(ListId='a4d24015f8')

Bulk upsert members from any ODBC data source

<p>Subscribes or unsubscribes members from any ODBC data source (e.g. DSN) using <code>UPSERT INTO ListMembers SOURCE('ODBC', ...)</code>. The inner <code>SELECT</code> column aliases must match <code>ListMembers</code> input columns. Pass <code>ListId</code> in the <code>WITH</code> clause.</p> 

UPSERT INTO ListMembers
SOURCE('ODBC', 'DSN=MyOdbcDsn'
,'select col1 as EmailAddress,col2 as FirstName, col3 as Status from sometable') --//column name alias must match with InputColumns of ListMembers
WITH(ListId='a4d24015f8')

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Read a list member by ID

<p>Gets a single member by subscriber hash ID. Provide <code>ListId</code> in the <code>WITH</code> clause and <code>Id</code> in <code>WHERE</code> or <code>WITH</code>.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM ListMembers 
WHERE Id=''170a0722daae03855d6434eb3a5959fb'' 
WITH (ListId=''e246f7e24d'')';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

Read a list member by email

<p>Gets a single member by email address. Pass <code>ListId</code> and <code>Id</code> (email) in the <code>WITH</code> clause.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM ListMembers 
WITH (ListId=''e246f7e24d'', Id=''test@abc.com'')';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

Delete list member (archive)

<p>Archives a list member by ID using <code>DELETE FROM ListMembers WHERE Id='...' WITH (ListId='...')</code>. Archived or bounced records cannot be resubscribed; to resubscribe, use the permanent delete operation first to remove the member permanently.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM ListMembers 
WHERE Id=''170a0722daae03855d6434eb3a5959fb'' 
WITH (ListId=''e246f7e24d'')';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

Delete list members (archive) by date

<p>Archives list members that opted in after a specific date using <code>DELETE FROM ListMembers WHERE TimestampOpt > '...' WITH (ListId='...')</code>. For permanent delete, use the permanent delete operation instead.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE from ListMembers 
WHERE TimestampOpt > ''2023-06-16'' 
WITH (ListId=''a4d24015f8'')';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

Read members across all lists

<p>Gets members from all lists by querying <code>ListMembers</code> without <code>ListId</code>. The query scans all lists and fetches members for each; this may return a large result set.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM ListMembers --scan all lists and then fetch members for each list';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

Read a member by ID across all lists

<p>Gets a specific member by ID (subscriber hash) across all lists. Use <code>WHERE Id='...'</code> and omit <code>ListId</code>.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM ListMembers 
WHERE Id=''170a0722daae03855d6434eb3a5959fb''';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

Read members in a list (selected columns)

<p>Gets only the columns you specify for members in a list. Pass <code>ListId</code> in the <code>WITH</code> clause and list desired columns in the <code>SELECT</code> clause.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT Id, ListId, EmailAddress, UniqueEmailId, ContactId, FullName, WebId, EmailType, Status, ConsentsToOneToOneMessaging, FirstName, LastName, AddressLine1, AddressLine2, City, State, Zip, Country, Phone, Birthday, StatsAvgOpenRate, StatsAvgClickRate, IpSignup, TimestampSignup, IpOpt, TimestampOpt, MemberRating, LastChanged, Language, Vip, EmailClient, Latitude, Longitude, LocationGmtOff, LocationDstOff, CountryCode, TimeZone, Region, Source, TagsCount, Tags 
FROM ListMembers 
WITH (ListId=''e246f7e24d'')';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

Create list member

<p>Adds a new member to a list. Required columns include <code>EmailAddress</code> and <code>Status</code> (e.g. subscribed, pending). Pass <code>ListId</code> in the <code>WITH</code> clause. Optional: <code>MergeFields</code>, <code>Tags</code>, <code>Language</code>, <code>Vip</code>, signup/opt timestamps.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO ListMembers
(EmailAddress, Status, EmailType, MergeFields, Language, Vip, Latitude, Longitude, IpSignup, TimestampSignup, IpOpt, TimestampOpt, Tags)
VALUES
(''np-brucewayne1@zappysys.com'', ''subscribed'', ''html'',
''{"FNAME":"John","LNAME":"Doe","ADDRESS":{"addr1":"123","Freddie":"Ave","city":"Atlanta","state":"GA","zip":"12345"}}'',
''en'', false, ''41.881832'', ''-87.623177'', ''192.168.0.8'', ''2023-04-01 18:00:00'', ''192.168.0.8'', ''2023-04-02 14:00:00'',
''["Newtag1","Newtag2","Newtag3"]'')
WITH (ListId=''a4d24015f8'')';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

Update list member

<p>Updates an existing list member. Use <code>UPDATE ListMembers SET ... WHERE Id='...' WITH (ListId='...')</code>. You can change <code>EmailAddress</code>, <code>Status</code>, <code>EmailType</code>, merge fields, <code>Tags</code>, and other profile fields.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE ListMembers 
SET EmailAddress=''brucewayne10@mycompany.com''
, Status=''subscribed'' --subscribed, unsubscribed, cleaned, pending
, EmailType=''text'' --html, text
, Vip=''false'' --true
, FirstName=''Bruce''
, LastName=''Wayne''
/*,MergeFields= ''{
        "FNAME": "Bruce1",
        "LNAME": "Wayne1",
        "PHONE": "678-111-1234"
    }'',
*/
, Language=''en'' --fr
, TimestampOpt=''2023-04-02 11:37:49'' 
, Latitude=''38.8951'' , Longitude=''-77.0364'' --Washington DC
, Tags=''["tag1","tag2"]''
WHERE Id=''e9f73ced3b649f0ca829103bcacb2846'' 
WITH (ListId=''a4d24015f8'',SkipMergeValidation=''false'')';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

Bulk upsert members from SQL Server

<p>Subscribes or unsubscribes members in bulk using <code>UPSERT INTO ListMembers SOURCE('MSSQL', ...)</code>. Data is read from the SQL Server database; result set column names (or aliases) must match <code>ListMembers</code> input columns (e.g. <code>EmailAddress</code>, <code>FirstName</code>, <code>Status</code>). Pass <code>ListId</code> in the <code>WITH</code> clause.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO ListMembers
SOURCE(''MSSQL'', ''Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true''
,''select ''''brucewayne10@gmail.com'''' EmailAddress,''''first1'''' as FirstName, ''''subscribed'''' Status 
UNION ALL
select ''''test55@gmail.com'''' EmailAddress,''''first1'''' FirstName, ''''subscribed'''' Status 
'')
WITH(ListId=''a4d24015f8'')

 --//column name alias must match with InputColumns of ListMembers';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

Bulk upsert members from CSV

<p>Subscribes or unsubscribes members from a CSV file using <code>UPSERT INTO ListMembers SOURCE('ODBC', ...)</code> with the ZappySys CSV Driver. Map CSV columns to <code>ListMembers</code> input columns (e.g. <code>col1 as EmailAddress</code>, <code>col2 as FirstName</code>, <code>col3 as Status</code>). Pass <code>ListId</code> in the <code>WITH</code> clause.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO ListMembers
SOURCE(''ODBC'', ''Driver={ZappySys CSV Driver};DataPath=c:\subscribers.csv''
,''select col1 as EmailAddress,col2 as FirstName, col3 as Status from $'') --//column name alias must match with InputColumns of ListMembers
WITH(ListId=''a4d24015f8'')';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

Bulk upsert members from any ODBC data source

<p>Subscribes or unsubscribes members from any ODBC data source (e.g. DSN) using <code>UPSERT INTO ListMembers SOURCE('ODBC', ...)</code>. The inner <code>SELECT</code> column aliases must match <code>ListMembers</code> input columns. Pass <code>ListId</code> in the <code>WITH</code> clause.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO ListMembers
SOURCE(''ODBC'', ''DSN=MyOdbcDsn''
,''select col1 as EmailAddress,col2 as FirstName, col3 as Status from sometable'') --//column name alias must match with InputColumns of ListMembers
WITH(ListId=''a4d24015f8'')';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];