Easysoft Blog

Wed, Mar 28 2018

Updating SQL Server Data when Salesforce Data Changes

This blog shows you how to update a SQL Server table with changes in a Salesforce table.

  1. Connect SQL Server to Salesforce
  2. Create a local table in SQL Server: 
    create procedure SFMakeLocal( @Link varchar(50), @Remote varchar(50), @Local varchar(50), @DropLocal int) as
    declare @SQL as nvarchar(max)
    begin
        /* Imports the data into a local table */
        /* Set DropLocal to 1 to drop the local table if it exists */

        if OBJECT_ID(@Local, 'U') IS NOT NULL 
        begin
            if (@DropLocal=1) 
            begin
                set @SQL='DROP TABLE dbo.'+@Local
                exec ( @SQL)
            end
        else
            RAISERROR(15600,1,1, 'Local table already exists')
            RETURN  
        end

        set @SQL='select * into dbo.'+@Local+' from OPENQUERY('+@Link+',''select * from '+@Remote+''')'
        select @SQL
        exec(@SQL)
    end

-- @Link Your SQL Server linked server
-- @Remote The name of the table within Salesforce
-- @Local The local table you want the data to be stored in
-- @DropLocal Set to 1 if the table exists and you want to drop it
  3. Run the procedure to copy the record structure from the Salesforce table into the local table and then transfer all the data. This example command uses the Account table. 
    SFMakeLocal 'SF','Account','LocalAccount', 0

    Replace SF, with the name of your linked server to Salesforce.com.

  4. These procedures update the local SQL Server table with changes in the Salesforce.com data: 
    create procedure SFUpdateTable ( @Link varchar(50), @Remote varchar(50),
@Local varchar(50), @TempName varchar(50) ) as
    begin
        /* Updates the data into a local table based on changes in Salesforce */

        if OBJECT_ID(@TempName, 'U') IS NOT NULL 
        begin
            RAISERROR (15600,1,1, 'Temp table name already exists')
            RETURN  
        end

        declare @MaxCreated as datetime
        declare @MaxModified as datetime
        declare @SQL as nvarchar(max)

        select @MaxCreated=max(CreatedDate) from Account
        select @MaxModified=max(LastModifiedDate) from Account
        set @SQL='select * into '+@TempName+' from openquery(SF,''select * from Account where CreatedDate>{ts'''''+convert(varchar(22),@MaxCreated,120)+'''''}'')'
        exec(@SQL)

        exec SFAppendFromTemp @Local, @TempName

        set @SQL='select * into '+@TempName+' from openquery(SF,''select * from Account where LastModifiedDate>{ts'''''+convert(varchar(22),@MaxModified,120)+'''''} and CreatedDate<={ts'''''+convert(varchar(22),@MaxCreated,120)+'''''}'')'
        exec(@SQL)
        exec SFAppendFromTemp @Local, @TempName
    end

    create procedure SFAppendFromTemp(@Local varchar(50), @TempName varchar(50)) as 
    begin

        /* Uses the temp table to import the data into the local table making sure any duplicates are removed first */

        declare @Columns nvarchar(max)
        declare @ColName varchar(50)
        declare @SQL nvarchar(max)

        set @sql='delete from '+@Local+' where Id in ( select Id from '+@TempName+')'
        exec (@SQL)

        set @Columns=''

        declare col_cursor cursor for 
            select syscolumns.name from sysobjects inner join syscolumns on sysobjects.id = syscolumns.id where sysobjects.xtype = 'u' and  sysobjects.name = @Local

        open col_cursor
        fetch next from col_cursor into @ColName
        while @@FETCH_STATUS=0
        Begin
            set @Columns=@Columns+'['+@ColName+']'
            fetch next from col_cursor into @ColName
            if (@@FETCH_STATUS=0)
                set @Columns=@Columns+', '
        End
        close col_cursor
        deallocate col_cursor

        set @sql='insert into '+@Local+' (' +@Columns+') select '+@Columns+' from '+@TempName
        exec (@sql)

        set @sql='drop table '+@TempName
        exec (@sql)
    end

-- Two procedures are used to get the data from a remote table. 1) SFUpdateTable, which
-- copies the data into a temporary table. 2) SFAppendFromTemp, which appends
-- the data from the temporary table into the local table.

-- @Link Your SQL Server linked server name
-- @Remote The name of the table within Salesforce
-- @Local The local table where you want the data to be stored in
-- @TempName A name of a table that can be used to temporary store data. Do not
-- use an actual temporary table name such as #temp, this will not work.

    This example command copies the data from the Salesforce Account table via TempAccount into the LocalAccount table:

    SFUpdateTable 'SF','Account','LocalAccount','TempAccount'

    5. See Also

    Our insert related articles are:

    Additionally, an update example is here: