Salesforce ODBC Drivers BULK INSERT / Custom IDs
Posted: Mon 16 Apr 2018 14:15
We got the ODBC drivers for basically just reading data from salesforce (SELECT statement) into our SQL Server. The project changed and now we are inserting data into salesforce. Daily Inserts are couple hundred records so there is no issue there.
We have one monthly update that may extend from 10,000-50,000. Currently we tested an INSERT for 12000 rows and it took 1.5 hours before the connection terminated.
1)My question is : are there any best practices documentation (couldn't find any) for inserting "large" data using SSMS? Bulk Insert?
Driver :1.5.13
win server 2012
SQL Server 2014
Query :
INSERT INTO [dsf]...[Opportunity]
(AccountID,Name,StageName,Amount,Probability,CloseDate,Automotive_Classification__c,Channel__c,Customer_Ship_To__c,End_Customer__c,Opportunity_ID__c,Loss_Reason__c,Vapor_Thermal__c,VP_Car_Truck__c,VP_Design_Parent__c,VP_Global_Sales_Sub_Segment__c,VP_Manufacturer__c,VP_Manufacturer_Group__c,VP_Primary_Design_Center__c,VP_Primary_Design_Country__c,VP_Primary_Design_Region__c)
SELECT * FROM
Salesforce.DBO.ODBCTEST
2) can we use custom IDs for inserts like Opportunity_ID__c above instead of having to lookup salesforce db IDs? this is so we can link it to other tables.
Thank you
We have one monthly update that may extend from 10,000-50,000. Currently we tested an INSERT for 12000 rows and it took 1.5 hours before the connection terminated.
1)My question is : are there any best practices documentation (couldn't find any) for inserting "large" data using SSMS? Bulk Insert?
Driver :1.5.13
win server 2012
SQL Server 2014
Query :
INSERT INTO [dsf]...[Opportunity]
(AccountID,Name,StageName,Amount,Probability,CloseDate,Automotive_Classification__c,Channel__c,Customer_Ship_To__c,End_Customer__c,Opportunity_ID__c,Loss_Reason__c,Vapor_Thermal__c,VP_Car_Truck__c,VP_Design_Parent__c,VP_Global_Sales_Sub_Segment__c,VP_Manufacturer__c,VP_Manufacturer_Group__c,VP_Primary_Design_Center__c,VP_Primary_Design_Country__c,VP_Primary_Design_Region__c)
SELECT * FROM
Salesforce.DBO.ODBCTEST
2) can we use custom IDs for inserts like Opportunity_ID__c above instead of having to lookup salesforce db IDs? this is so we can link it to other tables.
Thank you