Update multiple products from CSV

Updates multiple products from an external source. Use UPDATE Products SOURCE('ODBC', ...) with a connection string and a SELECT whose column names (or aliases) match the product columns to update (e.g. Id, TaxCode). Works with ZappySys CSV Driver, SQL Server, MySQL, PostgreSQL, or any ODBC source.

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.

UPDATE Products
SOURCE('ODBC',

--File input example (use as to match Column name allowed in Products Table to write)
--'Driver={ZappySys CSV Driver};DataPath=c:\data\products.csv',
--'SELECT P_ID AS Id,P_TAXCODE AS TaxCode FROM $'

--Direct input example
'Driver={ZappySys CSV Driver}',
'SELECT ''abcd'' AS Id, ''DC010500'' AS TaxCode UNION
 SELECT ''xyz'' AS Id, ''DC010500'' AS TaxCode UNION
 SELECT ''zzz'' AS Id, ''DC010500'' AS TaxCode 
'
)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_FASTSPRING_IN_GATEWAY], 'UPDATE Products
SOURCE(''ODBC'',

--File input example (use as to match Column name allowed in Products Table to write)
--''Driver={ZappySys CSV Driver};DataPath=c:\data\products.csv'',
--''SELECT P_ID AS Id,P_TAXCODE AS TaxCode FROM $''

--Direct input example
''Driver={ZappySys CSV Driver}'',
''SELECT ''''abcd'''' AS Id, ''''DC010500'''' AS TaxCode UNION
 SELECT ''''xyz'''' AS Id, ''''DC010500'''' AS TaxCode UNION
 SELECT ''''zzz'''' AS Id, ''''DC010500'''' AS TaxCode 
''
)')

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) = 'UPDATE Products
SOURCE(''ODBC'',

--File input example (use as to match Column name allowed in Products Table to write)
--''Driver={ZappySys CSV Driver};DataPath=c:\data\products.csv'',
--''SELECT P_ID AS Id,P_TAXCODE AS TaxCode FROM $''

--Direct input example
''Driver={ZappySys CSV Driver}'',
''SELECT ''''abcd'''' AS Id, ''''DC010500'''' AS TaxCode UNION
 SELECT ''''xyz'''' AS Id, ''''DC010500'''' AS TaxCode UNION
 SELECT ''''zzz'''' AS Id, ''''DC010500'''' AS TaxCode 
''
)'
EXEC (@MyQuery) AT [LS_TO_FASTSPRING_IN_GATEWAY]