Reference

Table Contacts

Description

No description available

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.
Method Supported Reference EndPoint
SELECT get_contacts
INSERT create_contacts
UPDATE update_contacts
UPSERT
DELETE archive_contacts
LOOKUP get_contact

Examples

SSIS

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

Read from Contacts table using API Source

API Source - Hubspot
Read and write HubSpot data effortlessly. Integrate, manage, and automate contacts, companies, deals, and tickets — almost no coding required.
Hubspot
Contacts
Optional Parameters
Properties
SSIS API Source - Read from table or endpoint

Read/write to Contacts table using API Destination

API Destination - Hubspot
Read and write HubSpot data effortlessly. Integrate, manage, and automate contacts, companies, deals, and tickets — almost no coding required.
Hubspot
Contacts
Select
Optional Parameters
Properties
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

List contacts

<p>Returns all contacts. Use <code>WHERE Id=...</code> to limit to a single contact by ID.</p> 

SELECT * FROM Contacts
-- WHERE Id=123

Get a contact by ID

<p>Returns a single contact by ID. Supply the contact ID in the <code>WHERE</code> clause.</p> 

SELECT * FROM Contacts
WHERE Id=51

Insert a contact

<p>Creates a single contact with the given email, first name, last name, and optional custom properties. Column names must match the contact property internal names.</p> 

INSERT INTO Contacts(email, firstname, lastname, custom_checkbox, custom_date, custom_number)
VALUES ('john.doe@abc.com', 'John', 'Doe', 'true', '2023-01-01', 100.55)

Update a contact

<p>Updates an existing contact by ID. Set the properties you want to change in the <code>SET</code> clause; uncomment other lines to update additional custom properties.</p> 

UPDATE Contacts
SET firstname='Brian',
    lastname='Halligan (Sample Contact)'
    --, custom_number=12.12
    --, custom_date='2023-01-01'
    --, custom_string='My Test<<FUN_NOW>>'
    --, custom_richtext='<p><span style="color: #b5b5b5;">##-Please reply-##</span></p>'
    --, custom_multi_select='Blue;Yellow;Black'
    --, custom_pickuser=45581955
    --, custom_dropdown='BB'
    --, custom_checkbox='true'
WHERE Id=51

Delete a contact

<p>Deletes a contact by ID. Supply the contact ID in the <code>WHERE</code> clause.</p> 

DELETE FROM Contacts
WHERE Id=1234

Bulk create contacts using SQL Server data

<p>Inserts multiple contacts by reading rows from a SQL Server database. Data is read from a SELECT statement (or stored procedure); column names or aliases must match the target contact properties (e.g. <code>firstname</code>, <code>lastname</code>, <code>email</code>). The driver uses the HubSpot bulk API to send the data.</p> 

INSERT INTO Contacts
SOURCE (
  'MSSQL',
  'Data Source=localhost;Initial Catalog=Test;Integrated Security=true',
  'SELECT firstname, lastname, email FROM StagingContacts'
)

Bulk delete contacts using IDs from SQL Server

<p>Deletes multiple contacts by ID. IDs are read from a SQL Server SELECT statement or stored procedure. The result set must include an <code>Id</code> column (e.g. from a staging table such as <code>StagingContacts</code>) with the contact IDs to delete.</p> 

DELETE FROM Contacts
SOURCE (
  'MSSQL',
  'Data Source=localhost;Initial Catalog=Test;Integrated Security=true',
  'SELECT Id FROM StagingContacts'
)

Bulk update contacts using SQL Server data

<p>Updates multiple contacts by reading IDs and new values from a SQL Server SELECT statement or stored procedure. Column names or aliases must match contact properties (e.g. <code>Id</code>, <code>email</code>). The driver uses the HubSpot bulk API to apply the updates.</p> 

UPDATE Contacts
SOURCE (
  'MSSQL',
  'Data Source=localhost;Initial Catalog=Test;Integrated Security=true',
  'SELECT Id, email FROM StagingContacts'
)

SQL Server

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

List contacts

<p>Returns all contacts. Use <code>WHERE Id=...</code> to limit to a single contact by ID.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Contacts
-- WHERE Id=123';

EXEC (@MyQuery) AT [LS_TO_HUBSPOT_IN_GATEWAY];

Get a contact by ID

<p>Returns a single contact by ID. Supply the contact ID in the <code>WHERE</code> clause.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Contacts
WHERE Id=51';

EXEC (@MyQuery) AT [LS_TO_HUBSPOT_IN_GATEWAY];

Insert a contact

<p>Creates a single contact with the given email, first name, last name, and optional custom properties. Column names must match the contact property internal names.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Contacts(email, firstname, lastname, custom_checkbox, custom_date, custom_number)
VALUES (''john.doe@abc.com'', ''John'', ''Doe'', ''true'', ''2023-01-01'', 100.55)';

EXEC (@MyQuery) AT [LS_TO_HUBSPOT_IN_GATEWAY];

Update a contact

<p>Updates an existing contact by ID. Set the properties you want to change in the <code>SET</code> clause; uncomment other lines to update additional custom properties.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Contacts
SET firstname=''Brian'',
    lastname=''Halligan (Sample Contact)''
    --, custom_number=12.12
    --, custom_date=''2023-01-01''
    --, custom_string=''My Test<<FUN_NOW>>''
    --, custom_richtext=''<p><span style="color: #b5b5b5;">##-Please reply-##</span></p>''
    --, custom_multi_select=''Blue;Yellow;Black''
    --, custom_pickuser=45581955
    --, custom_dropdown=''BB''
    --, custom_checkbox=''true''
WHERE Id=51';

EXEC (@MyQuery) AT [LS_TO_HUBSPOT_IN_GATEWAY];

Delete a contact

<p>Deletes a contact by ID. Supply the contact ID in the <code>WHERE</code> clause.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Contacts
WHERE Id=1234';

EXEC (@MyQuery) AT [LS_TO_HUBSPOT_IN_GATEWAY];

Bulk create contacts using SQL Server data

<p>Inserts multiple contacts by reading rows from a SQL Server database. Data is read from a SELECT statement (or stored procedure); column names or aliases must match the target contact properties (e.g. <code>firstname</code>, <code>lastname</code>, <code>email</code>). The driver uses the HubSpot bulk API to send the data.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Contacts
SOURCE (
  ''MSSQL'',
  ''Data Source=localhost;Initial Catalog=Test;Integrated Security=true'',
  ''SELECT firstname, lastname, email FROM StagingContacts''
)';

EXEC (@MyQuery) AT [LS_TO_HUBSPOT_IN_GATEWAY];

Bulk delete contacts using IDs from SQL Server

<p>Deletes multiple contacts by ID. IDs are read from a SQL Server SELECT statement or stored procedure. The result set must include an <code>Id</code> column (e.g. from a staging table such as <code>StagingContacts</code>) with the contact IDs to delete.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Contacts
SOURCE (
  ''MSSQL'',
  ''Data Source=localhost;Initial Catalog=Test;Integrated Security=true'',
  ''SELECT Id FROM StagingContacts''
)';

EXEC (@MyQuery) AT [LS_TO_HUBSPOT_IN_GATEWAY];

Bulk update contacts using SQL Server data

<p>Updates multiple contacts by reading IDs and new values from a SQL Server SELECT statement or stored procedure. Column names or aliases must match contact properties (e.g. <code>Id</code>, <code>email</code>). The driver uses the HubSpot bulk API to apply the updates.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Contacts
SOURCE (
  ''MSSQL'',
  ''Data Source=localhost;Initial Catalog=Test;Integrated Security=true'',
  ''SELECT Id, email FROM StagingContacts''
)';

EXEC (@MyQuery) AT [LS_TO_HUBSPOT_IN_GATEWAY];