Reference

Table Customers

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_customers
INSERT post_customer
UPDATE put_customer
UPSERT
DELETE delete_customer
LOOKUP get_customers

Examples

SSIS

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

Read from Customers table using API Source

API Source - Shopify
Read and write Shopify data effortlessly. Integrate, manage, and automate customers, orders, products, and inventory — almost no coding required.
Shopify
Customers
Optional Parameters
Customer Id(s) - Comma separated
SSIS API Source - Read from table or endpoint

Read/write to Customers table using API Destination

API Destination - Shopify
Read and write Shopify data effortlessly. Integrate, manage, and automate customers, orders, products, and inventory — almost no coding required.
Shopify
Customers
Select
Optional Parameters
Customer Id(s) - Comma separated
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read customers

<p>Gets a list of all customers from the <code>Customers</code> table.</p> 

SELECT * FROM Customers

Read a customer by ID

<p>Gets a specific customer by their unique <code>Id</code>. Supply the customer ID in the <code>WHERE</code> clause.</p> 

SELECT * FROM Customers Where Id=12345

Read multiple customers by IDs

<p>Gets multiple customers by supplying a comma-separated list of IDs in the <code>ids</code> parameter within the <code>WITH</code> clause.</p> 

SELECT * FROM Customers
WITH (ids='1111111111111,2222222222222,3333333333333')

Create a customer

<p>Creates a new customer record. You can specify details such as <code>FirstName</code>, <code>LastName</code>, <code>Email</code>, <code>Phone</code>, and address fields like <code>DefaultAddressCity</code> and <code>DefaultAddressCountry</code>.</p> 

INSERT INTO Customers
(FirstName, LastName, Email, Phone, Password, PasswordConfirmation, SendWelcomeEmail, MultipassIdentifier, Note, Tags, TaxExempt, TaxExemptions, DefaultAddressFirstName, DefaultAddressLastName, DefaultAddressCompany, DefaultAddressLine1, DefaultAddressLine2, DefaultAddressCity, DefaultAddressProvince, DefaultAddressCountry, DefaultAddressZip, DefaultAddressPhone, DefaultAddressName, DefaultAddressProvinceCode, DefaultAddressCountryCode, DefaultAddressCountryName)
VALUES
('John', 'Doe', 'john.doe@gmail.com', '7705553543', 'myNewP@ssword123', 'myNewP@ssword123', 1, null, 'This is a note on the customer account.', null, 0, null, 'John', 'Doe', 'John Doe Corp.', '123 Main Street', null, 'Atlanta', 'Georgia', 'United States', '30135', '7705553543', 'John Doe', 'GA', 'US', 'United States')

Create a customer using raw JSON body

<p>Creates a customer by supplying the entire JSON body in the special <code>_rawdoc_</code> column. This is useful for setting attributes or nested structures that are not mapped to specific input columns.</p> 

INSERT INTO Customers(_rawdoc_)
VALUES('{"customer":{"first_name":"John","last_name":"Doe","email":"a.doe@gmail.com","phone":"7705553111"}}')

Bulk create customers using SQL Server data

<p>Bulk creates customers by reading data from an external SQL Server database using the <code>SOURCE</code> clause. The column names in the source query must match the input columns of the <code>Customers</code> table (e.g. <code>FirstName</code>, <code>LastName</code>, <code>Email</code>).</p> 

INSERT INTO Customers(FirstName, LastName, Email, Phone)
SOURCE('MSSQL'
  ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
  ,'select ''John'' as FirstName, ''Doe'' as LastName, ''a.doe@gmail.com'' as Email, ''7705553111'' as Phone'
)

Bulk create customers using raw JSON body from SQL Server

<p>Bulk creates customers by reading raw JSON bodies from an external SQL Server database. The source query should return a column named <code>_rawdoc_</code> containing the JSON for each customer.</p> 

INSERT INTO Customers
SOURCE('MSSQL'
	,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
	,'select ''{"customer":{"first_name":"Cust1","last_name":"Doe1","email":"a.doe@gmail.com","phone":"7705553111"}}'' as _rawdoc_
	 UNION 
	 select ''{"customer":{"first_name":"Cust2","last_name":"Doe2","email":"b.doe@gmail.com","phone":"7705553222"}}'' as _rawdoc_
	 '
	)

Update an existing customer record 

UPDATE Customers SET
  Email = 'john.doe2@gmail.com',
  Phone = '7705553445',
  Note= 'This is a new note that needed to be added later.'
  WHERE Id=1111111111111

Update a customer using raw JSON body

<p>Updates a customer by supplying the entire JSON body in the special <code>_rawdoc_</code> column. This allows updating attributes not exposed as standard columns.</p> 

UPDATE Customers
SET _rawdoc_='{"customer":{"first_name":"John_new","last_name":"Doe_new","email":"a_new.doe@gmail.com","phone":"7705553111"}}'
WHERE Id=1111111111111

Bulk update customers using SQL Server data

<p>Bulk updates customers by reading data from an external SQL Server database. The source query must return the <code>Id</code> of the customer to update along with the fields to change (e.g. <code>Email</code>, <code>Note</code>).</p> 

UPDATE Customers
SOURCE('MSSQL'
 ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
 ,'select 111 as Id, ''a@a.com''Email , ''SOLD'' as Note,0 as [$$ContineOn404Error]
  UNION
  select 222 as Id, ''b@b.com''Email , ''SOLD'' as Note,0 as [$$ContineOn404Error]
  '
)

Delete a customer

<p>Deletes a customer record by its <code>Id</code>.</p> 

DELETE Customers WHERE Id=1111111111111

Delete a customer (throw error if not found)

<p>Deletes a customer record by ID. By default, no error is thrown if the ID is not found; set <code>ContineOn404Error=0</code> in the <code>WITH</code> clause to force an error in that case.</p> 

DELETE Customers 
WHERE Id=1111111111111 
WITH(ContineOn404Error=0)

Bulk delete customers using IDs from SQL Server

<p>Bulk deletes customers by reading <code>Id</code> values from an external SQL Server database. You can also pass <code>$$ContineOn404Error</code> as a column to control error handling per row.</p> 

DELETE FROM Customers
SOURCE('MSSQL'
  ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
  ,'select 111 as Id,1 as [$$ContineOn404Error]
    UNION
    select 222 as Id,1 as [$$ContineOn404Error]
   '
)

SQL Server

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

Read customers

<p>Gets a list of all customers from the <code>Customers</code> table.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Customers';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Read a customer by ID

<p>Gets a specific customer by their unique <code>Id</code>. Supply the customer ID in the <code>WHERE</code> clause.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Customers Where Id=12345';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Read multiple customers by IDs

<p>Gets multiple customers by supplying a comma-separated list of IDs in the <code>ids</code> parameter within the <code>WITH</code> clause.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Customers
WITH (ids=''1111111111111,2222222222222,3333333333333'')';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Create a customer

<p>Creates a new customer record. You can specify details such as <code>FirstName</code>, <code>LastName</code>, <code>Email</code>, <code>Phone</code>, and address fields like <code>DefaultAddressCity</code> and <code>DefaultAddressCountry</code>.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Customers
(FirstName, LastName, Email, Phone, Password, PasswordConfirmation, SendWelcomeEmail, MultipassIdentifier, Note, Tags, TaxExempt, TaxExemptions, DefaultAddressFirstName, DefaultAddressLastName, DefaultAddressCompany, DefaultAddressLine1, DefaultAddressLine2, DefaultAddressCity, DefaultAddressProvince, DefaultAddressCountry, DefaultAddressZip, DefaultAddressPhone, DefaultAddressName, DefaultAddressProvinceCode, DefaultAddressCountryCode, DefaultAddressCountryName)
VALUES
(''John'', ''Doe'', ''john.doe@gmail.com'', ''7705553543'', ''myNewP@ssword123'', ''myNewP@ssword123'', 1, null, ''This is a note on the customer account.'', null, 0, null, ''John'', ''Doe'', ''John Doe Corp.'', ''123 Main Street'', null, ''Atlanta'', ''Georgia'', ''United States'', ''30135'', ''7705553543'', ''John Doe'', ''GA'', ''US'', ''United States'')';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Create a customer using raw JSON body

<p>Creates a customer by supplying the entire JSON body in the special <code>_rawdoc_</code> column. This is useful for setting attributes or nested structures that are not mapped to specific input columns.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Customers(_rawdoc_)
VALUES(''{"customer":{"first_name":"John","last_name":"Doe","email":"a.doe@gmail.com","phone":"7705553111"}}'')';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Bulk create customers using SQL Server data

<p>Bulk creates customers by reading data from an external SQL Server database using the <code>SOURCE</code> clause. The column names in the source query must match the input columns of the <code>Customers</code> table (e.g. <code>FirstName</code>, <code>LastName</code>, <code>Email</code>).</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Customers(FirstName, LastName, Email, Phone)
SOURCE(''MSSQL''
  ,''Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true''
  ,''select ''''John'''' as FirstName, ''''Doe'''' as LastName, ''''a.doe@gmail.com'''' as Email, ''''7705553111'''' as Phone''
)';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Bulk create customers using raw JSON body from SQL Server

<p>Bulk creates customers by reading raw JSON bodies from an external SQL Server database. The source query should return a column named <code>_rawdoc_</code> containing the JSON for each customer.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Customers
SOURCE(''MSSQL''
	,''Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true''
	,''select ''''{"customer":{"first_name":"Cust1","last_name":"Doe1","email":"a.doe@gmail.com","phone":"7705553111"}}'''' as _rawdoc_
	 UNION 
	 select ''''{"customer":{"first_name":"Cust2","last_name":"Doe2","email":"b.doe@gmail.com","phone":"7705553222"}}'''' as _rawdoc_
	 ''
	)';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Update an existing customer record 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Customers SET
  Email = ''john.doe2@gmail.com'',
  Phone = ''7705553445'',
  Note= ''This is a new note that needed to be added later.''
  WHERE Id=1111111111111';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Update a customer using raw JSON body

<p>Updates a customer by supplying the entire JSON body in the special <code>_rawdoc_</code> column. This allows updating attributes not exposed as standard columns.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Customers
SET _rawdoc_=''{"customer":{"first_name":"John_new","last_name":"Doe_new","email":"a_new.doe@gmail.com","phone":"7705553111"}}''
WHERE Id=1111111111111';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Bulk update customers using SQL Server data

<p>Bulk updates customers by reading data from an external SQL Server database. The source query must return the <code>Id</code> of the customer to update along with the fields to change (e.g. <code>Email</code>, <code>Note</code>).</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Customers
SOURCE(''MSSQL''
 ,''Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true''
 ,''select 111 as Id, ''''a@a.com''''Email , ''''SOLD'''' as Note,0 as [$$ContineOn404Error]
  UNION
  select 222 as Id, ''''b@b.com''''Email , ''''SOLD'''' as Note,0 as [$$ContineOn404Error]
  ''
)';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Delete a customer

<p>Deletes a customer record by its <code>Id</code>.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE Customers WHERE Id=1111111111111';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Delete a customer (throw error if not found)

<p>Deletes a customer record by ID. By default, no error is thrown if the ID is not found; set <code>ContineOn404Error=0</code> in the <code>WITH</code> clause to force an error in that case.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE Customers 
WHERE Id=1111111111111 
WITH(ContineOn404Error=0)';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Bulk delete customers using IDs from SQL Server

<p>Bulk deletes customers by reading <code>Id</code> values from an external SQL Server database. You can also pass <code>$$ContineOn404Error</code> as a column to control error handling per row.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Customers
SOURCE(''MSSQL''
  ,''Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true''
  ,''select 111 as Id,1 as [$$ContineOn404Error]
    UNION
    select 222 as Id,1 as [$$ContineOn404Error]
   ''
)';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];