Updating and Deleting rows using SQL
DBAmp supports updating and deleting Salesforce.com objects with SQL.
In order to get the maximum performance with your UPDATE and DELETE
statements, you need to understand how SQL Server handles
UPDATE/DELETE statements with a linked server (like DBAmp).
For example, take the following SQL UPDATE
Update SALESFORCE…Account
Set AnnualRevenue = 4000
Where Id=’00130000005ZsG8AAK’
Using the Display Estimated Execution Plan option from the Query
Analyzer, you can see that SQL Server will retrieve the entire Account
table from Salesforce and then search for the one row that has the Id of
00130000005ZsG8AAK. Then, SQL Server will update the AnnualRevenue of
that row.
Obviously, this UPDATE statement has poor performance which gets worse
as the size of the Account table grows. What we need is a way to retrieve
only the row with Id 00130000005ZsG8AAK and then update the
AnnualRevenue of that row. To do this, use an OPENQUERY clause as the
table name.
Update OPENQUERY(SALESFORCE,
‘Select Id, AnnualRevenue from Account
where Id=”00130000005ZsG8AAK” ‘)
set AnnualRevenue = 4000
Using an OPENQUERY clause insures that we retrieve only the row with the
proper Id.
You can construct stored procedures that make your code more readable
and that use the above technique. See the Create SF_UpdateAccount.sql
file in the DBAmp program directory as an example. Using this stored
procedure, we can do updates to the Account table using the following SQL:
exec SF_UpdateAccount ‘00130000008hz55AAA’,’BillingCity’,”’Denver”’
or
exec SF_UpdateAccount ‘00130000008hz55AAA’,’AnnualRevenue’,’20000′
You can use the SF_UpdateAccount stored procedure as a template for
building your own specialized stored procedures. See the file Create
SF_UpdateAnnualRevenue.sql for an example. Then, use the following
SQL to update the Annual Revenue of an account.
exec SF_UpdateAnnualRevenue ‘00130000009DCEcAAO’, 30000
Deleting rows with SQL has the same caveats. For best performance with
deletion by Id, use an OPENQUERY clause in the SQL statement. An
example of a stored procedure that deletes Accounts by Id is in the file
Create SF_DeleteAccount.sql.
For maximum scalability, please consider using the sf_TableLoader stored
procedure instead of SQL Update or Delete statements. The
sf_TableLoader stored procedure takes advantage of the ability to batch
together requests to the salesforce.com api.
Database Blog 