Create or update product (upsert)

Creates a new product or updates an existing one by ID (upsert). Use UPSERT INTO Products with the same columns as for insert or update. If the ID exists, the row is updated; otherwise a new product is created.

Standard SQL query example

This is the base query accepted by the connector. To execute it in SQL Server, you have to pass it to the Data Gateway via a Linked Server. See how to accomplish this using the examples below.

--Insert or Update
--UPSERT INTO Products 

--Insert or Update
UPSERT INTO Products
(     [Id]	, [Display]	, [Summary]
	, [Description]	, [Action]	, [Format]
	, [Sku]	, [TaxCode]	, [Image]
	, [Renew]	, [Interval]	, [IntervalLength]
	, [IntervalCount]	, [QuantityBehavior]	, [QuantityDefault]
	, [PriceUSD]	, [PriceEUR]	, [PriceGBP]
	, [PriceCAD]	, [PriceNZD]	, [PriceCHF]
	, [PriceSEK]	, [PriceCZK]	, [PriceDKK]
	, [PricePLN]	, [PriceSGD]	, [PriceJPY]
	, [PriceCNY]	, [PriceHKD]	, [PriceINR]	, [PriceBRL]	, [PriceAUD]
	, [Trial]	
	, [TrialPriceUSD]	, [TrialPriceEUR]	, [TrialPriceGBP]
	, [PaymentCollected]	, [PaidTrial]
	, [FulfillmentInstructions]
	, [DateLimitsEnabled]	, [CancellationInterval]	, [CancellationIntervalLength]	
	, [SetupFeeTitle]	
	, [SetupFeePriceUSD]	, [SetupFeePriceEUR]	, [SetupFeePriceGBP]	
	, [Badge]	, [Rank]	
	, [Attributes]		
	)
VALUES('test-std-id'
	, '3Year **standard** subscription edition'
	, 'summary **standard edition** _some italic_ ' -- markdown syntax allowed
	
	, 'long description **test STD** and many more lines' --markdown syntax allowed
	, 'action text with **markdown** '
	, 'digital' -- e.g. digital OR physical OR digital-and-physical
	
	, 'TEST-STD-001'
	, 'DC010500'
	, 'https://zappysys.com/api/images/ZappySys-icon.png'
	
	, true	, 'year'	, 1
	, 1 	, 'allow' -- e.g. allow Or lock Or hide
	, 3	--stop renewal after 3 periods
	, 10.12	, 11.12	, 12.12	
	, 13.12	, 14.12	, 15.12	
	, 16.12	, 17.12	, 18.12	
	, 19.12	, 20.12	, 21.12	
	, 22.12	, 23.12	, 24.12	, 25.12	, 26.12	
    , 14 --how many days trial allowed
    , 10.12	, 11.12	, 12.12	
    , 'true', true
	, 'Thank you for shopping, **here are** license instructions' --markdown supported
	, false, 'day', 5
	, 'Setup title'	
	--setup fees in common currencies
	, 10.50	, 11.50	, 12.50	
	, 'badge-1'	, 1
	, '{"mykey-1" : "some-value-1", "mykey-2" : "some-value-2"}' --JSON fragment 
	--, '[... fullfillments.. ]' --JSON fragment 	
)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_FASTSPRING_IN_GATEWAY], '--Insert or Update
--UPSERT INTO Products 

--Insert or Update
UPSERT INTO Products
(     [Id]	, [Display]	, [Summary]
	, [Description]	, [Action]	, [Format]
	, [Sku]	, [TaxCode]	, [Image]
	, [Renew]	, [Interval]	, [IntervalLength]
	, [IntervalCount]	, [QuantityBehavior]	, [QuantityDefault]
	, [PriceUSD]	, [PriceEUR]	, [PriceGBP]
	, [PriceCAD]	, [PriceNZD]	, [PriceCHF]
	, [PriceSEK]	, [PriceCZK]	, [PriceDKK]
	, [PricePLN]	, [PriceSGD]	, [PriceJPY]
	, [PriceCNY]	, [PriceHKD]	, [PriceINR]	, [PriceBRL]	, [PriceAUD]
	, [Trial]	
	, [TrialPriceUSD]	, [TrialPriceEUR]	, [TrialPriceGBP]
	, [PaymentCollected]	, [PaidTrial]
	, [FulfillmentInstructions]
	, [DateLimitsEnabled]	, [CancellationInterval]	, [CancellationIntervalLength]	
	, [SetupFeeTitle]	
	, [SetupFeePriceUSD]	, [SetupFeePriceEUR]	, [SetupFeePriceGBP]	
	, [Badge]	, [Rank]	
	, [Attributes]		
	)
VALUES(''test-std-id''
	, ''3Year **standard** subscription edition''
	, ''summary **standard edition** _some italic_ '' -- markdown syntax allowed
	
	, ''long description **test STD** and many more lines'' --markdown syntax allowed
	, ''action text with **markdown** ''
	, ''digital'' -- e.g. digital OR physical OR digital-and-physical
	
	, ''TEST-STD-001''
	, ''DC010500''
	, ''https://zappysys.com/api/images/ZappySys-icon.png''
	
	, true	, ''year''	, 1
	, 1 	, ''allow'' -- e.g. allow Or lock Or hide
	, 3	--stop renewal after 3 periods
	, 10.12	, 11.12	, 12.12	
	, 13.12	, 14.12	, 15.12	
	, 16.12	, 17.12	, 18.12	
	, 19.12	, 20.12	, 21.12	
	, 22.12	, 23.12	, 24.12	, 25.12	, 26.12	
    , 14 --how many days trial allowed
    , 10.12	, 11.12	, 12.12	
    , ''true'', true
	, ''Thank you for shopping, **here are** license instructions'' --markdown supported
	, false, ''day'', 5
	, ''Setup title''	
	--setup fees in common currencies
	, 10.50	, 11.50	, 12.50	
	, ''badge-1''	, 1
	, ''{"mykey-1" : "some-value-1", "mykey-2" : "some-value-2"}'' --JSON fragment 
	--, ''[... fullfillments.. ]'' --JSON fragment 	
)')

Using EXEC in SQL Server (handling larger SQL text)

The major drawback of OPENQUERY is its inability to incorporate variables within SQL statements. This often leads to the use of cumbersome dynamic SQL (with numerous ticks and escape characters).

Fortunately, starting with SQL 2005 and onwards, you can utilize the EXEC (your_sql) AT [LS_TO_FASTSPRING_IN_GATEWAY] syntax.

DECLARE @MyQuery NVARCHAR(MAX) = '--Insert or Update
--UPSERT INTO Products 

--Insert or Update
UPSERT INTO Products
(     [Id]	, [Display]	, [Summary]
	, [Description]	, [Action]	, [Format]
	, [Sku]	, [TaxCode]	, [Image]
	, [Renew]	, [Interval]	, [IntervalLength]
	, [IntervalCount]	, [QuantityBehavior]	, [QuantityDefault]
	, [PriceUSD]	, [PriceEUR]	, [PriceGBP]
	, [PriceCAD]	, [PriceNZD]	, [PriceCHF]
	, [PriceSEK]	, [PriceCZK]	, [PriceDKK]
	, [PricePLN]	, [PriceSGD]	, [PriceJPY]
	, [PriceCNY]	, [PriceHKD]	, [PriceINR]	, [PriceBRL]	, [PriceAUD]
	, [Trial]	
	, [TrialPriceUSD]	, [TrialPriceEUR]	, [TrialPriceGBP]
	, [PaymentCollected]	, [PaidTrial]
	, [FulfillmentInstructions]
	, [DateLimitsEnabled]	, [CancellationInterval]	, [CancellationIntervalLength]	
	, [SetupFeeTitle]	
	, [SetupFeePriceUSD]	, [SetupFeePriceEUR]	, [SetupFeePriceGBP]	
	, [Badge]	, [Rank]	
	, [Attributes]		
	)
VALUES(''test-std-id''
	, ''3Year **standard** subscription edition''
	, ''summary **standard edition** _some italic_ '' -- markdown syntax allowed
	
	, ''long description **test STD** and many more lines'' --markdown syntax allowed
	, ''action text with **markdown** ''
	, ''digital'' -- e.g. digital OR physical OR digital-and-physical
	
	, ''TEST-STD-001''
	, ''DC010500''
	, ''https://zappysys.com/api/images/ZappySys-icon.png''
	
	, true	, ''year''	, 1
	, 1 	, ''allow'' -- e.g. allow Or lock Or hide
	, 3	--stop renewal after 3 periods
	, 10.12	, 11.12	, 12.12	
	, 13.12	, 14.12	, 15.12	
	, 16.12	, 17.12	, 18.12	
	, 19.12	, 20.12	, 21.12	
	, 22.12	, 23.12	, 24.12	, 25.12	, 26.12	
    , 14 --how many days trial allowed
    , 10.12	, 11.12	, 12.12	
    , ''true'', true
	, ''Thank you for shopping, **here are** license instructions'' --markdown supported
	, false, ''day'', 5
	, ''Setup title''	
	--setup fees in common currencies
	, 10.50	, 11.50	, 12.50	
	, ''badge-1''	, 1
	, ''{"mykey-1" : "some-value-1", "mykey-2" : "some-value-2"}'' --JSON fragment 
	--, ''[... fullfillments.. ]'' --JSON fragment 	
)'
EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY]