Table InventoryLevels

Parameters

Supported Operations

Examples

SSIS

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

Read from InventoryLevels table using API Source

Shopify

InventoryLevels

Read/write to InventoryLevels table using API Destination

Shopify

InventoryLevels

Select

ODBC application

Use these SQL queries in your ODBC application data source:

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)

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

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)

select * from InventoryLevels WITH (inventory_item_ids='43512280416356, 44648752676964')

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)

select * from InventoryLevels WITH (location_ids='43512280416356, 44648752676964')

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

select * from InventoryLevels WITH (inventory_item_ids='43512280416356, 44648752676964' , location_ids='111100034, 111100055')

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

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

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

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

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 )
*/

Connects an inventory item to a location

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

UPDATE InventoryLevels 
SET LocationId=25801916516
WHERE InventoryItemId=43512276942948
WITH(
 Action='connect' --or adjust or set
, ContineOn404Error=0 
)

SQL Server

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

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

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

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

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

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

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

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