Hubspot Connector

SELECT * FROM Companies --Where Id=123

SELECT * FROM Contacts --Where Id=123

SELECT * FROM Contacts Where Id=51

SELECT Id
	, IsArchived
	, CreatedAt
	, UpdatedAt
	, email
	, firstname
	, lastname
	, company_size
	--, custom_checkbox
	--, custom_multi_select
	--, custom_number
	--, custom_pickuser
	--, custom_richtext
	--, custom_string
	--, custom_string_multi
FROM search_contacts
With(SearchFilters='[ {"propertyName": "email", "operator": "EQ", "value": "bh@hubspot.com" } ]')

SELECT * FROM Deals --Where Id=123

SELECT * FROM LineItems --Where Id=123

SELECT * FROM Owners

SELECT * FROM Products --Where Id=123

SELECT * FROM Tickets

SELECT * FROM Quotes

SELECT *
FROM generic_request
WITH(
     Url='crm/v3/objects/companies/search'
    ,Filter='$.results[*]'
    ,RequestContentTypeCode='ApplicationJson'
    ,RequestMethod='POST'
    ,RequestData='{
        "filters": [ {"propertyName": "hs_lastmodifieddate", "operator": "GT", "value": "<<yearstart-1y||unix_timestamp_ms,FUN_TO_DATE>>" } ],
        "properties": ["address","city","state"]  
        }'
)

SELECT *
FROM generic_request
WITH(
	  Url='filemanager/api/v3/files/upload'
	, RequestMethod='POST'
	, Filter ='$.objects[*]'
	, Body='file=@c:\temp\dump.png
folderPath=/
fileName=my_dump.png
options={"access": "PRIVATE", "overwrite": true}'
	, IsMultiPart='True'
	, Meta='id:long; name:string; title:string; url: string; type:string; size:long; friendly_url:string; meta.allows_anonymous_access:string; meta.indexable:bool'
)

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 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 FROM Contacts Where Id=1234

INSERT INTO Contacts 
SOURCE(
  'MSSQL' --ODBC or OLEDB
  ,'Data Source=localhost;Initial Catalog=Test;Integrated Security=true'
  ,' 
    ''bob'' as firstname, ''rock'' as firstname, ''new-email1@abc.com'' as email
    UNION 
    ''sam'' as firstname, ''smith'' as firstname, ''new-email2@abc.com'' as email
)

DELETE FROM Contacts 
SOURCE(
  'MSSQL' --ODBC or OLEDB
  ,'Data Source=localhost;Initial Catalog=Test;Integrated Security=true'
  ,'select 104897 as Id 
    UNION 
    select 104898 as Id'
)

UPDATE Contacts 
SOURCE(
  'MSSQL' --ODBC or OLEDB
  ,'Data Source=localhost;Initial Catalog=Test;Integrated Security=true'
  ,'select 104811 as Id, ''new-email1@abc.com'' as email
    UNION 
    select 104812 as Id, ''new-email2@abc.com'' as email
)