Help get this topic noticed by sharing it on Twitter, Facebook, or email.

extract data from salesforce with a condition from local table

I am trying to extract data from salesforce to a local table on SQL server based on a condition of customer account reference number. These customer reference numbers are stored in a .csv on my local machine and then imported to a local temporary table. I query salesforce org based on these reference numbers stored in the local temp table. Once found they are inserted in another local table called A_AccountIDs. The issue is it takes 3 seconds if there is only one record where as it takes 17 minutes for 11 records. I tried using OPENQuery but its producing a blank table.
Any suggestions is highly appreciated
TRUNCATE TABLE A_AccountIds
CREATE TABLE A_AccountIds_temp
(
Customer_Acc_Number varchar(50) NOT NULL
)

BULK INSERT A_AccountIds_temp FROM 'C:\BCP\Account_Ids.csv' WITH (FIRSTROW=2 , FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

INSERT INTO A_AccountIds(Account_Id,Account_Name) SELECT distinct Id, Name
FROM SALESFORCE...Account where JVCO_Customer_Account_Number__c in (SELECT distinct Customer_Acc_Number from A_AccountIds_temp)

DROP TABLE A_AccountIds_temp

Using Open Query :
INSERT INTO A_AccountIds(Account_Id,Account_Name) SELECT * FROM OpenQuery(SALESFORCE,
'SELECT Account.Id, Account.Name FROM Account where Account.JVCO_Customer_Account_Number__c in (''SELECT Customer_Acc_Number from A_AccountIds_temp'') ')
1 person has
this question
+1
Reply