Using INSERT INTO from SQL Server to change Salesforce data

Recently, a Salesforce ODBC driver customer contacted our Support team to ask how to insert SQL Server BLOB data into Salesforce, where the source for the data was the results of a query. This blog shares that solution.

In our example solution, the source for the INSERT INTO statement was this table:

CREATE TABLE
    NewProducts (
        "Name" nvarchar (30),
        ProductCode nvarchar (10),
        Description nvarchar (max)
    )
INSERT INTO
    NewProducts
VALUES
    (
        'Easysoft ODBC-Salesforce Driver',
        'EAS01',
        'ODBC driver for Salesforce. Available for Linux, UNIX, and Windows.'
    )
INSERT INTO
    NewProducts
VALUES
    (
        'Easysoft ODBC-SQL Server Driver',
        'EAS02',
        'ODBC driver for SQL Server. Available for Linux, UNIX, and Windows.'
    )
INSERT INTO
    NewProducts
VALUES
    (
        'Easysoft ODBC-Oracle Driver',
        'EAS03',
        'ODBC driver for Oracle. Available for Linux, UNIX, and Windows.'
    )
SELECT
    *
FROM
    NewProducts

To insert the data from this SQL Server table into Salesforce, we ran:

declare @Name nvarchar(30)
declare @ProductCode nvarchar(10)
declare @Description nvarchar(max)

declare ins_cursor cursor for 
select "Name", ProductCode, Description from NewProducts
open ins_cursor
fetch next from ins_cursor into @Name, @ProductCode, @Description
while @@FETCH_STATUS=0
Begin
exec ('insert into Product2 ( "Name", ProductCode, Description ) values (?, ?, ?)',
       @Name, @ProductCode, @Description ) at MySalesforceLinkedServer
fetch next from ins_cursor into @Name, @ProductCode, @Description
End
close ins_cursor
deallocate ins_cursor

Further information