Getting started with SQL-Sales and SQL Server Express

This data sheet shows how easy it is to get started with SQL-Sales and working with your Salesforce data, even with the minimum entry free SQL Server Express edition. We assume you have access to a SQL Server, if not read “Don’t have access to a SQL Server?” data sheet for guidance on installing SQL Server Express 2022.

We’re starting here: you have SQL Server Express, you’ve created a Database (DEMO in this case) and can open a query window.

Open the enabling script ss_EnableDatabase.sql

See here for SQL-Sales installation instructions and here for detailed information on enabling a database

Once opened click Execute, or hit F5

If this is your first time enabling a database for use with SQL-Sales on this SQL Server, you’ll likely encounter this message:

Follow these instructions (this is a one-time activity). Copy the provided text and paste into a new Query (you can quickly open a new query window with Ctrl-N)

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
--To update the currently configured value for advanced options.
RECONFIGURE
GO
--To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
--To update the currently configured value for this feature.
RECONFIGURE
GO

Click Execute / F5

That’s it, now return to ss_EnableDatabase.sql and Execute again

Let’s get started straight-away by pulling Account data. Note the first ever use of SQL-Sales will take a few additional seconds, to start the SQL-Sales Daemon in the background (the Daemon handles all requests to Salesforce)

exec ss_Replica 'prod', 'Account'

In our test Salesforce instance there’s just 3 Accounts currently

select * from Account

See the newly created replica SQL table and columns by hitting F8 for the Object Explorer

In the next sections we’re going to demonstrate loading 10000 Accounts and 10000 linked Contacts as a foundational exercise in how to load to Salesforce and critically, leverage a core SQL-Sales feature of automatically updating your load table with newly created SF Ids, on Inserts.

In our example we have source data in table SourceAccountDataDemo and will prepare a load table called Account_demo_Insert, it comprises a Name and a legacy Id.  See the accompanying data sheet “Importing data to SQL Server” for further guidance on this subject.

We need to define an Id column (a column is a SQL term for a SF field) and an Error column, as below, for full help on Loading see here. For now, in addition to the Id and Error columns, we must ensure the Account load table name starts with the given SF object name (in this case “Account”) – followed by a single underscore.

drop table if exists Account_demo_Insert
select
convert(nchar(18),null) as Id
,convert(nvarchar(255),null) as Error
,Name
,LegacyAccountId as LegacyAccountId_Info
into Account_demo_Insert
from SourceAccountDataDemo

Our simple objective for Account_demo_Insert is to load each record to Salesforce as an Account. If you notice in the script to create the load table, we’ve included the source Legacy Account Id in the Insert payload – “LegacyAccountId_Info” is not a SF field but by including in the created Account_demo_Insert table, we can join to this downstream when we load Contacts. Of course for your data migration you may want to additionally load this value into an actual Account field.

exec ss_Loader 'insert', 'prod', 'Account_demo_Insert'

Note in the returned helper text that LegacyAccountId_Info has been recognised as not being a SF field on the Account object and so has been excluded from the insert. Now let’s select from the Account_demo_Insert table:

The newly created Account Ids have been automatically passed back to the Id column of your load data – this is a powerful aide in your use of SQL Server when working with Salesforce data

Next, let’s prepare the Contact payload. This time in our example, the source Contact data is in a table called SourceContactDataDemo. Notice this table includes the legacy Account Id – this is a very typical situation in source Account and Contact data and this is how we have joined the source Account and Contact data together. If you look at the select output and the SQL used to prepare the Contact_demo_Insert – we have passed in the new Account Id as the Contact.AccountId in the Contact insert payload table.

drop table if exists Contact_demo_Insert
select
convert(nchar(18),null) as Id
,convert(nvarchar(255),null) as Error
,a.Id as AccountId
,c.FirstName
,c.LastName
,c.Email
,c.LegacyContactId as LegacyContactId_Info
,c.LegacyAccountId as LegacyAccountId_Info
into Contact_demo_Insert
from SourceContactDataDemo c

join Account_demo_Insert a
on c.LegacyAccountId = a.LegacyAccountId_Info

Here is the insert command for the Contact load, for our prepared Contact payload:

exec ss_Loader 'insert', 'prod', 'Contact_demo_Insert'

Followed by a select from Contact_demo_Insert. As with Account, we now have the newly created Contact.Id. Our loading demonstration will end here but in reality as with most data migrations we would carry on, now maybe loading child data to Contact and joining together the load tables with source data as needed.

Above we’ve run a quick replica for Account and Contact.

Here’s the Salesforce data, within your SQL Server.

If you’re wondering how much space these 10000 Accounts have taken, remembering your free SQL Server Express only allows 10Gb data storage – right click the Account object in the Object Explorer, as above and you’ll see for our basic example, the replicated Accounts have taken just under 4Mb. The 10000 Contacts as shown below take up just over 6Mb.

You may have more data and most likely you’ll have more objects than our simple 2 object demonstration – and quickly you’ll build up various SQL tables – but that all said, 10Gb for many SF data requirements goes a long way.