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