Inserting SQL Server data into Salesforce with a cursor

This blog provides an example of transferring data from a local SQL Server table to Salesforce. We are using the Salesforce ODBC driver to insert three records into the Salesforce Product2 table.

  1. Configure a linked server that connects to Salesforce.
  2. In SQL Server Management Studio, change the Linked Server > Linked Server Properties > Server Options > RPC Out setting to True.
  3. Create this table in SQL Server: 
    CREATE TABLE
    NewProducts (
        "Name" nvarchar (30),
        ProductCode nvarchar (10),
        Description nvarchar (max)
    )
INSERT INTO
    NewProducts
VALUES
    ('Test1', 'TEST01', 'Test 1st description')
INSERT INTO
    NewProducts
VALUES
    ('Test2', 'TEST02', '2nd description')
INSERT INTO
    NewProducts
VALUES
    ('Test3', 'TEST03', '3rd Test description')

    You can insert data into any of the columns in the Product2 table, assuming you have the necessary permissions.

  4. Execute the following SQL: 
    -- Declare a variable for each column you want to insert:
declare @Name nvarchar(30)
declare @ProductCode nvarchar(10)
declare @Description nvarchar(max)

-- Use a cursor to select your data, which enables SQL Server to extract
-- the data from your local table to the variables.
declare ins_cursor cursor for 
        select @quot;Name@quot;, ProductCode, Description from NewProducts
    open ins_cursor
    fetch next from ins_cursor into @Name, @ProductCode, @Description -- At this point, the data from the first row
                                                                      -- is in your local variables.

    -- Move through the table with the @@FETCH_STATUS=0 
    while @@FETCH_STATUS=0
    Begin

         -- Execute the insert to push this data into Salesforce. Replace @quot;SF_LINK@quot; with the name of your Salesforce Linked Server.
        exec ('insert into Product2 ( "Name", ProductCode, Description ) Values (?, ?, ?)', @Name, @ProductCode ,@Description ) at SF_LINK

         -- Once the execution has taken place, you fetch the next row of data from your local table.
        fetch next from ins_cursor into @Name, @ProductCode, @Description
    End

    -- When all the rows have inserted you must close and deallocate the cursor.
    -- Failure to do this will not let you re-use the cursor.    
    close ins_cursor
    deallocate ins_cursor

Further information