SQL Server guide

SQL examples for SQL Server

The ZappySys API Driver is a user-friendly interface designed to facilitate the seamless integration of various applications with the Shopify API. With its intuitive design and robust functionality, the ZappySys API Driver simplifies the process of configuring specific API endpoints to efficiently read or write data from Shopify.

On this page you will find some SQL examples which can be used for API ODBC Driver or Data Gateway API Connector.

Get list of products 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get a specific product by its ID 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Products WITH Id=1111111111111';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get multiple specific products by their IDs 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Create a new product

This example shows how to insert a new Shopify product. It also sets Variants 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Products 
(
	 Title
	,Status
	,BodyHtml
	,UrlHandle
	,Vendor
	,ProductType
	,Variants
	,Options
	,Tags
	,Metafields
	,Images	
	)
VALUES	
(''Ice Cream''
,''draft''
,''<strong>Very yummy ice cream!</strong>''
,''ice-cream'' 
,''Burton''
,''Snowboard'' 

,''[
	{"price":10.5, "option1":"Chocolate","option2":"Small","sku":"ICE-CHO-SML","inventory_quantity":100},
	{"price":10.5, "option1":"Chocolate","option2":"Medium","sku":"ICE-CHO-MED","inventory_quantity":100},
	{"price":11.5, "option1":"Vanilla","option2":"Small","sku":"ICE-VNL-MED","inventory_quantity":210}
  ]''

--you must set variants and use atlease one value from the below list in option1, option2 or option3 in any variant entry else it will fail.  
,''[
	{"name":"Color","values":["Chocolate","Vanilla"]}, 
	{"name":"Size","values":["Small","Medium"]}
  ]'' 

,''["Frozen","Seasonal","Dad''''s Fav"]''

--adding metadata (custom fields) - metadata fields must be created before setting it 
--below are 2 system fields for SEO Title / SEO Description (you dont need to create them unlike custom metadata). These values appears on SEO section
,''[
	{"key":"title_tag","value":"Yum Ice Cream SEO Title", "namespace":"global","type":"single_line_text_field"}, 
	{"key":"description_tag","value":"Yum Ice Cream SEO description", "namespace":"global","type":"single_line_text_field"} 
  ]''

--first image becomes main image if you supply multiple images 
--upload multiple images from URL (set "src")
 , ''[	
	{"src":"https://zappysys.com/images/tech/google-analytics-logo.png"},
	{"src":"https://zappysys.com/images/tech/web-api-logo.png"}
  ]''
--OR upload multiple local image files (set "attachment")
--, ''[	
--	 {"attachment":"<<c:\temp\icecream_1.png,FUN_FILE_BASE64ENC>>"},
--	 {"attachment":"<<c:\temp\icecream_2.png,FUN_FILE_BASE64ENC>>"}
--  ]''  
  
)';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Update an existing product

This example shows how to update an existing product. Update product title, description (body html), images, variants and more 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Products
SET Title=''Ice Cream - Updated''
 , Status=''draft'' --active, archived, draft  
 , BodyHtml=''<strong>Very yummy ice cream - updated!</strong>''  
--first image becomes main image if you supply multiple images 
--upload multiple images from URL (set "src")
 , Images=''[	
	{"src":"https://zappysys.com/images/tech/google-analytics-logo.png"},
	{"src":"https://zappysys.com/images/tech/web-api-logo.png"}
  ]''
--OR upload multiple local image files (set "attachment")
--, Images=''[	
--	 {"attachment":"<<c:\temp\icecream_1.png,FUN_FILE_BASE64ENC>>"},
--	 {"attachment":"<<c:\temp\icecream_2.png,FUN_FILE_BASE64ENC>>"}
--  ]''

 , Variants=''[
	{"price":20.5, "option1":"Chocolate","option2":"Small","sku":"ICE-CHO-SML","inventory_quantity":300},
	{"price":21.5, "option1":"Vanilla","option2":"Small","sku":"ICE-VNL-MED","inventory_quantity":110}
  ]''
, PublishedScope=''global'' --or web
, Vendor =''IceGlobal''
, ProductType =''Cold Food''
, Tags =''["Frozen","Seasonal","Dad''''s Fav"]''
--Update SEO URL
 ,UrlHandle=''ice-cream-51'' 
--Update SEO title / description
, SEOTitle=''Yum Ice Cream SEO Title-update''  
, SEODescription=''Yum Ice Cream SEO description-update''  
Where Id=7348335771748';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Delete an existing product

This example shows how to delete an existing product. 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Products
Where Id=7348335771748';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get list of all product variants 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get all product variants by a specific product ID 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM ProductVariants Where ProductId=''1111111111111''';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get all product variants by multiple specific product IDs 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Create a new product variant

This example shows how to create a new product variant. 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO ProductVariants (ProductId, Option1, Option2,SKU,Price,CompareAtPrice,Position,Weight,WeightUnit,ImageId)
Values(7348335771748, ''Chocolate'', ''Medium'', ''ICE-CHO-MED'', 195.5, 200.5, 3, 20.5, ''lb'', 31900013854820)';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Update product variant price, image, weight

This example shows how to update product variant price, image, weight and other attributes. 

DECLARE @MyQuery NVARCHAR(MAX) = 'Update ProductVariants 
SET  
	,Option1=''Chocolate'' 
	,Option2=''Large''
	,SKU=''ICE-CHO-SML''
	,Price=90.45
	,CompareAtPrice=100.45
	,Position=2
	,Weight=10.5
	,WeightUnit=''lb''
	,ImageId=31900013854820  --use available images from Products table
Where Id=42564507992164';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Delete an existing product variant

This example shows how to delete an existing product variant by Variant Id. 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM ProductVariants 
WHERE Id=31900013854820';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get list of customers 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get a specific customer by its ID 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get multiple specific customers by their IDs 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Insert a new customer record 

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];

Learn more about this SQL query.

Insert a new customer record using RAW JSON Body (special column _rawdoc_)

Sometimes you have need to INSERT or UPDATE certain arrtibutes for which input columns not defined. In this case you can supply entire BODY JSON as input using special column name _rawdoc_ 

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];

Learn more about this SQL query.

Insert customers in BULK (read from external MS SQL database)

In this example we are reading customer Name, Email, Phone from external source system (Microsoft SQL Server) and sending it to Shopify. Your column name must match with Input columns of the table. See other BULK examples to learn more about reading from other systems using ODBC or OLEDB connection. 

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];

Learn more about this SQL query.

Insert customers in BULK using RAW JSON Body (read from external MS SQL database)

In this example we are reading customer Name, Email, Phone from external source system (Microsoft SQL Server) and sending it to Shopify. Your column name must match with Input columns of the table. See other BULK examples to learn more about reading from other systems using ODBC or OLEDB connection. 

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];

Learn more about this SQL query.

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];

Learn more about this SQL query.

Update an existing customer record using RAW JSON Body (special column _rawdoc_)

Sometimes you have need to INSERT or UPDATE certain arrtibutes for which input columns not defined. In this case you can supply entire BODY JSON as input using special column name _rawdoc_ 

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];

Learn more about this SQL query.

Update an existing customer record 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Orders SET
  FulfillmentStatus = ''john.doe5@gmail.com'',
  Phone = ''7705553111'',
  Note= ''This is a new note that needed to be added to the order later.''
WHERE Id=1111111111111';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Update customers in BULK (read from external MS SQL database)

In this example we are reading customer Ids, Email, Notes from external source system (Microsoft SQL Server) and sending it to Shopify. Your column name must match with Input columns of the table you trying to update. See other BULK examples to learn more about reading from other systems using ODBC or OLEDB connection. 

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];

Learn more about this SQL query.

Delete a customer record 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Delete a customer record (throw error if not found) 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Delete customers in BULK (read Id from external MS SQL database)

In this example we are reading customer Ids from external source system (Microsoft SQL Server) and sending it to Shopify. See other BULK examples to learn more about reading from other systems using ODBC or OLEDB connection. 

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];

Learn more about this SQL query.

Get all orders 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get open orders 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Orders WITH (Status=''open'') --also try ''any'', ''open'', ''closed'', ''cancelled''';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get a specific order by its ID 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Orders Where Id=1111111111111';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get multiple specific orders by their IDs 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Delete an order record 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Delete an order record (throw error if not found) 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get line items for all orders 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get line items for a specific order by the order ID 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM OrderItems Where OrderId=1111111111111';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get line items for multiple specific orders by their order IDs 

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

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Insert a new order record 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Orders (BillingAddressLine1, BillingAddressLine2, BillingAddressCity, BillingAddressCompany, BillingAddressCountry, BillingAddressFirstName, BillingAddressLastName, BillingAddressPhone, BillingAddressProvince, BillingAddressZip, BillingAddressName, BillingAddressProvinceCode, BillingAddressCountryCode, BuyerAcceptsMarketing, LineItems, CustomerId, Email, EstimatedTaxes, FinancialStatus, FulfillmentStatus, Name, Note, Phone, Currency, PresentmentCurrency, ProcessedAt, ReferringSite, ShippingAddressLine1, ShippingAddressLine2, ShippingAddressCity, ShippingAddressCompany, ShippingAddressCountry, ShippingAddressFirstName, ShippingAddressLastName, ShippingAddressPhone, ShippingAddressProvince, ShippingAddressZip, ShippingAddressName, ShippingAddressProvinceCode, ShippingAddressCountryCode, Tags, TaxesIncluded, TotalWeight, SendReceipt, SendFulfillmentReceipt)
VALUES
(''123 Main Street'', ''Suite #54'', ''Memphis'', ''Acme, Inc.'', ''United States'', ''John'', ''Doe'', ''4045559876'', ''Tennessee'', ''38101'', ''John Doe'', ''GA'', ''US'', 1, ''[{"title":"Super Strong Glue","price":24.99,"grams":"100","quantity":1,"tax_lines":[{"price":13.5,"rate":0.06,"title":"State tax"}]}]'', 5945175474276, ''johndoe2@gmail.com'', 1, ''pending'', null, ''#40294'', ''This order needs to be expedited, so register it in the system as so.'', ''4045559876'', ''USD'', ''USD'', ''2023-02-27T11:00:00'', ''https://referringsite.com'', ''123 Main Street'', ''Suite #54'', ''Memphis'', ''Acme, Inc.'', ''United States'', ''John'', ''Doe'', ''4045559876'', ''Tennessee'', ''38101'', ''John Doe'', ''GA'', ''US'', NULL, 1, 20, 1, 1)';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get inventory levels for all locations

Query inventory levels for all locations. If you get URL Too long error then manually supply location ids in the query (see other example) 

DECLARE @MyQuery NVARCHAR(MAX) = 'select * from InventoryLevels 
		--WITH(location_ids=''43512280416356, 44648752676964, ..... upto 300 to 500 more - until you hit URL limit error'')';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get inventory level for multiple item inventory id(s)

If you get URL Too long error then reduce inventory_item ids in the query (approx 300-400 ids per call allowed) 

DECLARE @MyQuery NVARCHAR(MAX) = 'select * from InventoryLevels WITH (inventory_item_ids=''43512280416356, 44648752676964'')';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get inventory level for specific location id(s) (i.e. Physcical Store / POS )

If you get URL Too long error then reduce location ids in the query (approx 300-400 ids per call allowed) 

DECLARE @MyQuery NVARCHAR(MAX) = 'select * from InventoryLevels WITH (location_ids=''43512280416356, 44648752676964'')';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get inventory level for specific inventory / location id(s) 

DECLARE @MyQuery NVARCHAR(MAX) = 'select * from InventoryLevels WITH (inventory_item_ids=''43512280416356, 44648752676964'' , location_ids=''111100034, 111100055'')';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Adjust inventory level for a specific inventory / location id(s)

Adjusts the inventory level of an inventory item at a single location 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE InventoryLevels 
SET AvailableAdjustment=488, 
    LocationId=25801916516
WHERE InventoryItemId=43512276942948
WITH(
 Action=''Adjust'' --or set or connect
, ContineOn404Error=0 
)';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Set / insert inventory with a specific inventory item and location id

Sets the inventory level for an inventory item at a location. If the specified location is not connected, it will be automatically connected first. When connecting inventory items to locations 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE InventoryLevels 
SET LocationId=25801916516
  ,Available=488 
WHERE InventoryItemId=43512276942948
WITH(
 Action=''set'' --or adjust or connect
, ContineOn404Error=0 
)

--OR--
/*
INSERT INTO InventoryLevels (InventoryItemId,LocationId,Available)
VALUES(43512276942948, 25801916516, 488)
--WITH( ContineOn404Error=0 )
*/';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Connects an inventory item to a location

Connects an inventory item to a location by creating an inventory level at that location. 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE InventoryLevels 
SET LocationId=25801916516
WHERE InventoryItemId=43512276942948
WITH(
 Action=''connect'' --or adjust or set
, ContineOn404Error=0 
)';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Set inventory with a specific inventory item and location id - generic API

If you get URL Too long error then reduce location ids in the query (approx 300-400 ids per call allowed) 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM generic_request 
WITH (
    URL=''/inventory_levels/set.json''
  --OR Use full URL
  --URL=''https://MY-STORE-HERE.myshopify.com/admin/api/2023-01/inventory_levels/set.json''
  	
  , RequestMethod=''POST''
  , Body=''{"location_id":25801916516,"inventory_item_id":43512280416356,"available":42}'' --needed if you call PUT, POST
  , Filter=''$.inventory_level'' --change table name here
  , Headers=''Content-Type: application/json''
  , Meta=''inventory_item_id:long; location_id:long; available:int; updated_at: datetime''
)';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get inventory item by id

You can find Inventory Item Id in ProductVariants table. ProductVariant has One-to-One mapping with InventoryItems table 

DECLARE @MyQuery NVARCHAR(MAX) = 'select * from InventoryItems Where Id=43512280416356';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get inventory item by id

You can find Inventory Item Id in ProductVariants table. ProductVariant has One-to-One mapping with InventoryItems table 

DECLARE @MyQuery NVARCHAR(MAX) = 'select * from InventoryItems Where Id=43512280416356';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Get inventory items by multiple Ids

Query multiple InventoryItems by Ids (Comma separated list). You can find Inventory Item Id in ProductVariants table. ProductVariant has One-to-One mapping with InventoryItems table 

DECLARE @MyQuery NVARCHAR(MAX) = 'select * from InventoryItems WITH(Ids=''43512280416356, 43512280449124'')';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Update an existing inventory item cost and other attributes 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE InventoryItems
SET Cost=''25.55''
WHERE Id=43512280416356';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Generic Query using Shopify admin GraphQL API

This example shows how to invoke GraphQL query for very generic data read/write. For more information on GraphQL API visit this link https://shopify.dev/docs/api/admin/getting-started 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM generic_request 
WITH (
    URL=''/graphql.json''
  --OR Use full URL
  --URL=''https://MY-STORE-HERE.myshopify.com/admin/api/2023-10/graphql.json''
  
  , RequestMethod=''POST''
  , Filter=''$.data.products.nodes'' --change table name here e.g. products
  , Headers=''Content-Type: application/json''
  --change table name and columns below here e.g. products... and id, title etc
  -- change pagesize if needed (i.e. max 250)
  , Body=''{
 "query" : "<<{
 
 products(first: 250 [$tag$])
 {
    nodes {
      id
      title
      createdAt
    }
    
    pageInfo {
      hasNextPage
      endCursor
    }
    
  }
  
},FUN_JSONENC>>"
}''
	, NextUrlAttributeOrExpr=''$.data.products.pageInfo.endCursor'' --change table name 
	, NextUrlEndIndicator=''false''
	, StopIndicatorAttributeOrExpr=''$.data.products.pageInfo.hasNextPage'' --change table name 
	, UseConnection=''True''
	, EnablePageTokenForBody=''True''
	, HasDifferentNextPageInfo=''True''
	, NextPageBodyPart=''after: \"[$pagetoken$]\"''
	--Use metadata to speed up execution. To get Metadata Run query without Meta clause. 
	-- Then click View Metadata button found in Botttom Result Grid Toolbar. Get Compact format and paste below
	--, Meta=''id:String(255); title:String(255); createdAt:DateTime; ''
)';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Generic Query using Shopify admin REST API

This example shows how to invoke pretty much any REST API for generic data read/write. For more information on REST API visit this link https://shopify.dev/docs/api/admin/getting-started 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM generic_request 
WITH (
    URL=''/products.json''
  --OR Use full URL
  --URL=''https://MY-STORE-HERE.myshopify.com/admin/api/2023-01/products.json''
  	
  , RequestMethod=''GET''
  , Body=''{}'' --needed if you call PUT, POST
  , Filter=''$.products[*]'' --change table name here
  , Headers=''Content-Type: application/json''
  , PagingMode=''ByResponseHeaderRfc5988''
  
  
  --Use metadata to speed up execution. To get Metadata Run query without Meta clause. 
  --Then click View Metadata button found in Botttom Result Grid Toolbar. Get Compact format and paste below
  , Meta=''id:String(255); title:String(255); created_at:DateTime; ''
)';

EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY];

Learn more about this SQL query.

Getting Started with Examples

ZappySys API Driver is a powerful software solution designed to facilitate the extraction and integration of data from a wide range of sources through APIs. Its intuitive design and extensive feature set make it an essential asset for any organization dealing with complex data integration tasks.

To get started with examples using ZappySys API Driver, please click on the following applications:

 SQL Server Connect Shopify in SQL Server
Power BI Connect Shopify in Power BI
SSRS Connect Shopify in SSRS
Informatica Connect Shopify in Informatica
MS Access Connect Shopify in MS Access
MS Excel Connect Shopify in MS Excel
SSAS Connect Shopify in SSAS
C# Connect Shopify in C#
Python Connect Shopify in Python
JAVA Connect Shopify in JAVA
Tableau Connect Shopify in Tableau
SAP Crystal Reports Connect Shopify in SAP Crystal Reports
Azure Data Factory (Pipeline) Connect Shopify in Azure Data Factory (Pipeline)
Talend Studio Connect Shopify in Talend Studio
UiPath Connect Shopify in UiPath
PowerShell Connect Shopify in PowerShell
ODBC Connect Shopify in ODBC

Key features of the ZappySys API Driver include:

The API ODBC driver facilitates the reading and writing of data from numerous popular online services (refer to the complete list here) using familiar SQL language without learning complexity of REST API calls. The driver allows querying nested structure and output as a flat table. You can also create your own ODBC / Data Gateway API connector file and use it with this driver.

  1. Intuitive Configuration: The interface is designed to be user-friendly, enabling users to easily set up the specific API endpoints within Shopify without requiring extensive technical expertise or programming knowledge.

  2. Customizable Endpoint Setup: Users can conveniently configure the API endpoint settings, including the HTTP request method, endpoint URL, and any necessary parameters, to precisely target the desired data within Shopify.

  3. Data Manipulation Capabilities: The ZappySys API Driver allows for seamless data retrieval and writing, enabling users to fetch data from Shopify and perform various data manipulation operations as needed, all through an intuitive and straightforward interface.

  4. Secure Authentication Integration: The driver provides secure authentication integration, allowing users to securely connect to the Shopify API by inputting the necessary authentication credentials, such as API tokens or other authentication keys.

  5. Error Handling Support: The interface is equipped with comprehensive error handling support, ensuring that any errors or exceptions encountered during the data retrieval or writing process are efficiently managed and appropriately communicated to users for prompt resolution.

  6. Data Visualization and Reporting: The ZappySys API Driver facilitates the seamless processing and presentation of the retrieved data from Shopify, enabling users to generate comprehensive reports and visualizations for further analysis and decision-making purposes.

Overall, the ZappySys API Driver serves as a powerful tool for streamlining the integration of applications with Shopify, providing users with a convenient and efficient way to access and manage data, all through a user-friendly and intuitive interface.