Products

Returns data from products table.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The Id of the product.
Name	String	False	The product name.
Type	String	False	The product type.
Sku	String	True	User-defined product code/stock keeping unit (SKU)
Description	String	False	Product description, which can include HTML formatting.
SearchKeywords	String	False	A comma-separated list of keywords that can be used to locate the product when searching the store.
AvailabilityDescription	String	False	Availability text, displayed on the checkout page under the product title, telling the customer how long it will normally take to ship this product.
Price	Decimal	False	The products price.
CostPrice	Decimal	False	The products cost price.
RetailPrice	Decimal	False	The products retail cost.
SalePrice	Decimal	False	Sale price.
CalculatedPrice	Decimal	True	Price as displayed to guests, adjusted for applicable sales and rules.
SortOrder	Integer	False	Priority to give this product when included in product lists on category pages and in search results.
IsVisible	Boolean	False	Flag to determine whether or not the product should be displayed to customers browsing.
IsFeatured	Boolean	False	Flag to determine whether the product should be included in the featured products panel for shoppers viewing the store.
RelatedProducts	String	False	Defaults to -1, which causes the store to automatically generate a list of related products.
InventoryLevel	String	False	Current inventory level of the product.
InventoryWarningLevel	String	False	Inventory Warning level for the product.
Warranty	String	False	Warranty information displayed on the product page.
Weight	Decimal	False	Weight of the product, which can be used when calculating shipping costs.
Width	Decimal	False	Width of the product, which can be used when calculating shipping costs.
Height	Decimal	False	Height of the product, which can be used when calculating shipping costs.
Depth	Decimal	False	Depth of the product, which can be used when calculating shipping costs.
FixedCostShippingPrice	Decimal	False	A fixed shipping cost for the product.
IsFreeShipping	Boolean	False	Flag used to indicate whether or not the product has free shipping.
InventoryTracking	String	False	The type of inventory tracking for the product.
RatingTotal	Integer	False	The total rating for the product.
RatingCount	Integer	False	The total number of ratings the product has had.
TotalSold	Integer	False	Total quantity of this product sold through transactions.
DateCreated	Datetime	False	The date of which the product was created.
BrandId	Integer	True	The products brand
ViewCount	Integer	False	The number of times the product has been viewed.
PageTitle	String	False	Custom title for the products page.
MetaKeywords	String	False	Custom meta keywords for the product page.
MetaDescription	String	False	Custom meta description for the product page.
LayoutFile	String	False	The layout template file used to render this product category.
IsPriceHidden	Boolean	False	The default false value indicates that this products price should be shown on the product page.
PriceHiddenLabel	String	False	By default, an empty string. If is_price_hidden is true, the value of price_hidden_label will be displayed instead of the price.
Categories	Int[]	False	An array of IDs for the categories this product belongs to. When updating a product, if an array of categories is supplied, then all product categories will be overwritten.
DateModified	Datetime	False	The date that the product was last modified.
EventDateFieldName	String	False	Name of the field to be displayed on the product page when selecting the event/delivery date.
EventDateType	String	False	Event Date type.
EventDateStart	Datetime	False	When the product requires the customer to select an event/delivery date, this date is used as the after date.
EventDateEnd	Datetime	False	When the product requires the customer to select an event/delivery date, this date is used as the before date.
MYOBAssetAccount	String	False	MYOB Asset Account.
MYOBIncomeAccount	String	False	MYOB Income Account.
MYOBExpenseAccount	String	False	MYOB Expense/COS Account.
PeachtreeGlAccount	String	False	Peachtree General Ledger Account.
Condition	String	False	The products condition.
IsConditionShown	Boolean	False	Flag used to determine whether the products condition will be shown to the customer on the product page.
PreorderReleaseDate	Datetime	False	Pre-order release date.
IsPreorderOnly	Boolean	False	If set to false, the product will not change its availability from preorder to available on the release date.
PreorderMessage	String	False	Custom expected-date message to display on the product page.
OrderQuantityMinimum	Integer	False	The minimum quantity an order must contain in order to purchase this product.
OrderQuantityMaximum	Integer	False	The maximum quantity an order can contain when purchasing the product.
OpenGraphType	String	False	Type of product.
OpenGraphTitle	String	False	Title of the product. If not specified, the products name will be used instead.
OpenGraphDescription	String	False	Description to use for the product.
IsOpenGraphThumbnail	Boolean	False	If set to true, the product thumbnail image will be used as the open graph image.
UPC	String	False	The product UPC code, which is used in feeds for shopping comparison sites.
DateLastImported	Datetime	False	The date on which the product was last imported using the bulk importer.
OptionSetId	Integer	True	The ID of the option set applied to the product.
TaxClassId	Integer	True	The ID of the tax class applied to the product.
OptionSetDisplay	String	True	The position on the product page where options from the option set will be displayed.
BinPickingNumber	String	False	The BIN picking number for the product.
CustomUrl	String	False	Custom URL (if set) overriding the structure dictated in the stores settings.
IsCustomized	Boolean	False	Returns true if the URL has been changed from its default state (the auto-assigned URL that BigCommerce provides.
Url	String	False	Product URL on the storefront.
Availability	String	False	Availability of the product.
PrimaryImageId	Integer	True	Id of the primary image.
PrimaryImageStandardUrl	String	True	Table Specific Information Select The driver will use the BigCommerce API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the driver. Id supports the '=, >=, >, <=, <' comparison. Name supports the '=' comparison. Sku supports the '=' comparison. Description supports the '=' comparison. Price supports the '=' comparison. IsVisible supports the '=' comparison. IsFeatured supports the '=' comparison. InventoryLevel supports the '=, >=, >, <=, <' comparison. DateCreated supports the '=, >=, >, <=, <' comparison. BrandId supports the '=' comparison. DateModified supports the '=, >=, >, <=, <' comparison. Condition supports the '=' comparison. DateLastImported supports the '=, >=, >, <=, <' comparison. Availability supports the '=' comparison. For example, the following queries are processed server side: SELECT * FROM Products SELECT * FROM Products WHERE Id > 5 AND Id < 10 SELECT * FROM Products WHERE IsVisible = "true" AND DateCreated >= "2020-01-01" AND DateCreated <= "2020-02-01" Insert To insert a new Product, you need to specify at least the following columns: Name, Type, Description, Price, Categories, Availability and Weight INSERT INTO Products (Name, Type, Description, Price, Categories, Availability, Weight) VALUES ("Plain T-Shirt","physical","This is a test description",29.99,18,"available",0.5) Bulk Update Insert into Update#TEMP(Description,id,name,sku,categories,RelatedProducts,MetaKeywords,IsCustomized,Url) values ('my_details','80','hello123','OTL','19,23','1,2','"pqr","xyz"',false,'/orbit-terrarium-large/'Insert into Update#TEMP(Description,id,name,sku,categories,RelatedProducts,MetaKeywords,IsCustomized,Url) values ('my_details1','86','example','ABS','23,21','3,4','"abc","an"',false,'/able-brewing-system/'UPDATE products (Description,id,name,sku,categories,RelatedProducts,MetaKeywords,IsCustomized,Url) Select Description,id,name,sku,categories,RelatedProducts,MetaKeywords,IsCustomized,Url from Update#TEMP Bulk Update Using Aggregates. Insert into Update#TEMP(Description,id,name,sku,categories,RelatedProducts,MetaKeywords,CustomUrl) values ('details1','77','name4456','SLCTBS','23,18','10','"abcd","ab"', '{ "is_customized": False, "url" : "/fog-linen-chambray-towel-beige-stripe/" }')UPDATE products (Description,id,name,sku,categories,RelatedProducts,CustomUrl) Select Description,id,name,sku,categories,RelatedProducts,CustomUrl from Update#TEMP Columns Name Type ReadOnly Description Id [KEY] Integer True The Id of the product. Name String False The product name. Type String False The product type. Sku String True User-defined product code/stock keeping unit (SKU) Description String False Product description, which can include HTML formatting. SearchKeywords String False A comma-separated list of keywords that can be used to locate the product when searching the store. AvailabilityDescription String False Availability text, displayed on the checkout page under the product title, telling the customer how long it will normally take to ship this product. Price Decimal False The products price. CostPrice Decimal False The products cost price. RetailPrice Decimal False The products retail cost. SalePrice Decimal False Sale price. CalculatedPrice Decimal True Price as displayed to guests, adjusted for applicable sales and rules. SortOrder Integer False Priority to give this product when included in product lists on category pages and in search results. IsVisible Boolean False Flag to determine whether or not the product should be displayed to customers browsing. IsFeatured Boolean False Flag to determine whether the product should be included in the featured products panel for shoppers viewing the store. RelatedProducts String False Defaults to -1, which causes the store to automatically generate a list of related products. InventoryLevel String False Current inventory level of the product. InventoryWarningLevel String False Inventory Warning level for the product. Warranty String False Warranty information displayed on the product page. Weight Decimal False Weight of the product, which can be used when calculating shipping costs. Width Decimal False Width of the product, which can be used when calculating shipping costs. Height Decimal False Height of the product, which can be used when calculating shipping costs. Depth Decimal False Depth of the product, which can be used when calculating shipping costs. FixedCostShippingPrice Decimal False A fixed shipping cost for the product. IsFreeShipping Boolean False Flag used to indicate whether or not the product has free shipping. InventoryTracking String False The type of inventory tracking for the product. RatingTotal Integer False The total rating for the product. RatingCount Integer False The total number of ratings the product has had. TotalSold Integer False Total quantity of this product sold through transactions. DateCreated Datetime False The date of which the product was created. BrandId Integer True The products brand ViewCount Integer False The number of times the product has been viewed. PageTitle String False Custom title for the products page. MetaKeywords String False Custom meta keywords for the product page. MetaDescription String False Custom meta description for the product page. LayoutFile String False The layout template file used to render this product category. IsPriceHidden Boolean False The default false value indicates that this products price should be shown on the product page. PriceHiddenLabel String False By default, an empty string. If is_price_hidden is true, the value of price_hidden_label will be displayed instead of the price. Categories Int[] False An array of IDs for the categories this product belongs to. When updating a product, if an array of categories is supplied, then all product categories will be overwritten. DateModified Datetime False The date that the product was last modified. EventDateFieldName String False Name of the field to be displayed on the product page when selecting the event/delivery date. EventDateType String False Event Date type. EventDateStart Datetime False When the product requires the customer to select an event/delivery date, this date is used as the after date. EventDateEnd Datetime False When the product requires the customer to select an event/delivery date, this date is used as the before date. MYOBAssetAccount String False MYOB Asset Account. MYOBIncomeAccount String False MYOB Income Account. MYOBExpenseAccount String False MYOB Expense/COS Account. PeachtreeGlAccount String False Peachtree General Ledger Account. Condition String False The products condition. IsConditionShown Boolean False Flag used to determine whether the products condition will be shown to the customer on the product page. PreorderReleaseDate Datetime False Pre-order release date. IsPreorderOnly Boolean False If set to false, the product will not change its availability from preorder to available on the release date. PreorderMessage String False Custom expected-date message to display on the product page. OrderQuantityMinimum Integer False The minimum quantity an order must contain in order to purchase this product. OrderQuantityMaximum Integer False The maximum quantity an order can contain when purchasing the product. OpenGraphType String False Type of product. OpenGraphTitle String False Title of the product. If not specified, the products name will be used instead. OpenGraphDescription String False Description to use for the product. IsOpenGraphThumbnail Boolean False If set to true, the product thumbnail image will be used as the open graph image. UPC String False The product UPC code, which is used in feeds for shopping comparison sites. DateLastImported Datetime False The date on which the product was last imported using the bulk importer. OptionSetId Integer True The ID of the option set applied to the product. TaxClassId Integer True The ID of the tax class applied to the product. OptionSetDisplay String True The position on the product page where options from the option set will be displayed. BinPickingNumber String False The BIN picking number for the product. CustomUrl String False Custom URL (if set) overriding the structure dictated in the stores settings. IsCustomized Boolean False Returns true if the URL has been changed from its default state (the auto-assigned URL that BigCommerce provides. Url String False Product URL on the storefront. Availability String False Availability of the product. PrimaryImageId Integer True Id of the primary image. PrimaryImageStandardUrl String True Standard url of the primary image.
SELECT * FROM Products SELECT * FROM Products WHERE Id > 5 AND Id < 10 SELECT * FROM Products WHERE IsVisible = "true" AND DateCreated >= "2020-01-01" AND DateCreated <= "2020-02-01"
INSERT INTO Products (Name, Type, Description, Price, Categories, Availability, Weight) VALUES ("Plain T-Shirt","physical","This is a test description",29.99,18,"available",0.5)
Insert into Update#TEMP(Description,id,name,sku,categories,RelatedProducts,MetaKeywords,IsCustomized,Url) values ('my_details','80','hello123','OTL','19,23','1,2','"pqr","xyz"',false,'/orbit-terrarium-large/'Insert into Update#TEMP(Description,id,name,sku,categories,RelatedProducts,MetaKeywords,IsCustomized,Url) values ('my_details1','86','example','ABS','23,21','3,4','"abc","an"',false,'/able-brewing-system/'UPDATE products (Description,id,name,sku,categories,RelatedProducts,MetaKeywords,IsCustomized,Url) Select Description,id,name,sku,categories,RelatedProducts,MetaKeywords,IsCustomized,Url from Update#TEMP
Insert into Update#TEMP(Description,id,name,sku,categories,RelatedProducts,MetaKeywords,CustomUrl) values ('details1','77','name4456','SLCTBS','23,18','10','"abcd","ab"', '{ "is_customized": False, "url" : "/fog-linen-chambray-towel-beige-stripe/" }')UPDATE products (Description,id,name,sku,categories,RelatedProducts,CustomUrl) Select Description,id,name,sku,categories,RelatedProducts,CustomUrl from Update#TEMP
Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The Id of the product.
Name	String	False	The product name.
Type	String	False	The product type.
Sku	String	True	User-defined product code/stock keeping unit (SKU)
Description	String	False	Product description, which can include HTML formatting.
SearchKeywords	String	False	A comma-separated list of keywords that can be used to locate the product when searching the store.
AvailabilityDescription	String	False	Availability text, displayed on the checkout page under the product title, telling the customer how long it will normally take to ship this product.
Price	Decimal	False	The products price.
CostPrice	Decimal	False	The products cost price.
RetailPrice	Decimal	False	The products retail cost.
SalePrice	Decimal	False	Sale price.
CalculatedPrice	Decimal	True	Price as displayed to guests, adjusted for applicable sales and rules.
SortOrder	Integer	False	Priority to give this product when included in product lists on category pages and in search results.
IsVisible	Boolean	False	Flag to determine whether or not the product should be displayed to customers browsing.
IsFeatured	Boolean	False	Flag to determine whether the product should be included in the featured products panel for shoppers viewing the store.
RelatedProducts	String	False	Defaults to -1, which causes the store to automatically generate a list of related products.
InventoryLevel	String	False	Current inventory level of the product.
InventoryWarningLevel	String	False	Inventory Warning level for the product.
Warranty	String	False	Warranty information displayed on the product page.
Weight	Decimal	False	Weight of the product, which can be used when calculating shipping costs.
Width	Decimal	False	Width of the product, which can be used when calculating shipping costs.
Height	Decimal	False	Height of the product, which can be used when calculating shipping costs.
Depth	Decimal	False	Depth of the product, which can be used when calculating shipping costs.
FixedCostShippingPrice	Decimal	False	A fixed shipping cost for the product.
IsFreeShipping	Boolean	False	Flag used to indicate whether or not the product has free shipping.
InventoryTracking	String	False	The type of inventory tracking for the product.
RatingTotal	Integer	False	The total rating for the product.
RatingCount	Integer	False	The total number of ratings the product has had.
TotalSold	Integer	False	Total quantity of this product sold through transactions.
DateCreated	Datetime	False	The date of which the product was created.
BrandId	Integer	True	The products brand
ViewCount	Integer	False	The number of times the product has been viewed.
PageTitle	String	False	Custom title for the products page.
MetaKeywords	String	False	Custom meta keywords for the product page.
MetaDescription	String	False	Custom meta description for the product page.
LayoutFile	String	False	The layout template file used to render this product category.
IsPriceHidden	Boolean	False	The default false value indicates that this products price should be shown on the product page.
PriceHiddenLabel	String	False	By default, an empty string. If is_price_hidden is true, the value of price_hidden_label will be displayed instead of the price.
Categories	Int[]	False	An array of IDs for the categories this product belongs to. When updating a product, if an array of categories is supplied, then all product categories will be overwritten.
DateModified	Datetime	False	The date that the product was last modified.
EventDateFieldName	String	False	Name of the field to be displayed on the product page when selecting the event/delivery date.
EventDateType	String	False	Event Date type.
EventDateStart	Datetime	False	When the product requires the customer to select an event/delivery date, this date is used as the after date.
EventDateEnd	Datetime	False	When the product requires the customer to select an event/delivery date, this date is used as the before date.
MYOBAssetAccount	String	False	MYOB Asset Account.
MYOBIncomeAccount	String	False	MYOB Income Account.
MYOBExpenseAccount	String	False	MYOB Expense/COS Account.
PeachtreeGlAccount	String	False	Peachtree General Ledger Account.
Condition	String	False	The products condition.
IsConditionShown	Boolean	False	Flag used to determine whether the products condition will be shown to the customer on the product page.
PreorderReleaseDate	Datetime	False	Pre-order release date.
IsPreorderOnly	Boolean	False	If set to false, the product will not change its availability from preorder to available on the release date.
PreorderMessage	String	False	Custom expected-date message to display on the product page.
OrderQuantityMinimum	Integer	False	The minimum quantity an order must contain in order to purchase this product.
OrderQuantityMaximum	Integer	False	The maximum quantity an order can contain when purchasing the product.
OpenGraphType	String	False	Type of product.
OpenGraphTitle	String	False	Title of the product. If not specified, the products name will be used instead.
OpenGraphDescription	String	False	Description to use for the product.
IsOpenGraphThumbnail	Boolean	False	If set to true, the product thumbnail image will be used as the open graph image.
UPC	String	False	The product UPC code, which is used in feeds for shopping comparison sites.
DateLastImported	Datetime	False	The date on which the product was last imported using the bulk importer.
OptionSetId	Integer	True	The ID of the option set applied to the product.
TaxClassId	Integer	True	The ID of the tax class applied to the product.
OptionSetDisplay	String	True	The position on the product page where options from the option set will be displayed.
BinPickingNumber	String	False	The BIN picking number for the product.
CustomUrl	String	False	Custom URL (if set) overriding the structure dictated in the stores settings.
IsCustomized	Boolean	False	Returns true if the URL has been changed from its default state (the auto-assigned URL that BigCommerce provides.
Url	String	False	Product URL on the storefront.
Availability	String	False	Availability of the product.
PrimaryImageId	Integer	True	Id of the primary image.
PrimaryImageStandardUrl	String	True	Standard url of the primary image.