If your team uses a database (PostgreSQL, MySQL, or MS SQL) and it is your single source of truth, keeping it up to date from various systems within your tech stack can be time-consuming.

Updating the data within your Google Sheets/Excel and pushing those updates directly to your database using the “Export to…” feature can make bulk updates to fields/tables within your database a breeze.💨 With actions, Update, Insert, and Delete, records can be managed directly from Google Sheets/Excel without writing additional queries.

What is Export to SQL DB?

How to Export to SQL DB?

Re-Use an Existing Mapping

Export to SQL DB Actions

Update

Insert

Delete

Schedule an Export to SQL DB

FAQs for Export to SQL DB

What is Export to SQL DB?

Export to SQL DB is a Coefficient feature that allows you to connect your database, pull your data into Google Sheets/Excel, update the values, and push those updates back to your database without the hassle of writing complicated scripts.

ℹ️ NOTE: You need to use your own database connection with Coefficient to push data from Google Sheets/Excel into your database using Coefficient. Shared data sources are NOT supported with this feature due to security reasons.

How to Export to SQL DB?

ℹ️ NOTE: We recommend doing an import of your data from your database into Google Sheets/Excel prior to using the Export to SQL DB. This allows us to automatically detect/map certain fields, rather than you having to map every field manually.
(Example: Insert new record to MySQL into the film table)

ℹ️ NOTE: Coefficient will automatically select the sheet you have open as the Tab for this Export. If it is a different tab, you will need to click the drop-down and select it.

ℹ️  Primary Keys:

UPDATE and DELETE Actions - Primary Keys may be required for your Export to SQL DB depending on how your primary key column(s) are set up in your database. 

INSERT Actions - This MAY or MAY NOT be required for your Export to SQL DB. If the primary key is set to auto-populate in your database, you can skip the mapping of the ID column, as the database SHOULD automatically create/assign a value for these newly inserted records. If you choose not to map the ID column, the ID value assigned to new records will take the last record and increment the new ID by +1.

10. The "Export Preview" window opens, displaying the fields that will be updated in your MySQL database (highlighted in blue). If there are potential errors with your export, they will be visible here, allowing you to make necessary adjustments to your data. When you have confirmed all the changes/updates that will be pushed to Salesforce, click the "Update 3 rows in MySQL" button.ℹ️  NOTE: Toggle the buttons at the top (enabled by default) to either "Show only the changed rows" or "Show only the mapped columns" for better visibility.

ℹ️ NOTE: This action CANNOT be undone, so ensure all your settings and updates are correct before proceeding.

11. When the Export to Salesforce is complete (and successful), you can see the number of rows exported/skipped. Congratulations on your first Export to SQL DB. 🎉

Re-Use an Existing Mapping 

If you have used the Export to function before then you will see the previous Export Mappings in the Coefficient sidebar located underneath your Import list. This list will allow you to reuse a previous mapping. We will go through the process of using both the sidebar and the button below.
1. Using the Export to SQL DB feature a second time will reveal the “Export” button in the Coefficient Header as well as the previous mappings in the sidebar.

 ℹ️ NOTE: The "Export" button will show if you have the "Include export button on sheet" setting enabled and if your data came from a Coefficient import.

2. When clicking on the “Export” on-sheet button the previous mapping(s) related to the sheet that you are currently on will appear in the sidebar. 3. Make sure to review the mappings before performing the Export to SQL DB again. (From here, you can view the run history and settings) then proceed with the typical “Export to…” process.

Export to SQL DB Actions

Update

Pushes updates to an existing field/record on your database.

• The record/s MUST have an ID field mapped to the primary key column on your database.

Insert

Creates a NEW ID and inserts data into a new record in your database.

• Depending on how your database is set up, you MAY skip mapping the ID column on your sheet and leave the value blank. If you choose not to map the ID column, the ID value assigned to new records usually will take the last record and increment the new ID by +1 (depending on the database configuration). The ID column is still required on the sheet even if you are not mapping it.

Delete

This action will delete the ENTIRE record from your database. Be careful with this one as it cannot be undone.

• The record MUST have a record ID mapped to your primary key column from your database.

Schedule an Export to SQL DB

If you work with data frequently, you may find yourself performing repetitive tasks. Our "Export to SQL DB" feature allows you to schedule pushing your data back to your database at a specific time. This feature can save you a significant amount of time by automating the export process. 😎

See how you can schedule an Export to SQL DB here.

FAQs for Export to SQL

What databases are supported with this feature?

The Export to SQL DB feature currently supports PostgreSQL, MySQL, and MS SQL.

Why can’t I use a shared connection with this feature?

For additional data security, Coefficient does not support shared connections in the Export to SQL DB feature. This security measure will help prevent unauthorized edits/deletions of records in your database by users who currently do not have permission to perform these actions.

Why are some columns greyed out when I try to map or update them?

In database tables, individual columns have sub-permissions. Columns may be read-only so that they will be greyed out and not mappable.

I am trying to Export to SQL DB (MySQL, PostgreSQL, etc.), and I have empty values in a few columns that are required fields. I am running into an error. How can I resolve this? 

This error is likely due to a permission issue in your database. To resolve it, please contact your DB Admin and request permissions to "CREATE TEMPORARY TABLES" using the following SQL query (there may be nuances depending on your server version):

GRANT CREATE TEMPORARY TABLES ON db_name.* TO 'user@email.com';

• You can replace "db_name" with the specific database name (if needed).

To grant access across all databases, you can use this query:

GRANT CREATE TEMPORARY TABLES ON * TO 'user@email.com';

I’m having issues with my SQL DB Writeback. What should I do?

• Confirm that you have the correct permissions in your database to use the Export to SQL DB feature. (You need to have Read/Write permissions) If you are not sure about your permissions, please contact your Database Admin to confirm.
• Check your settings; field mappings, primary key, ID, etc.
• Coefficient supports Export to SQL DB actions to single tables. Sheets that contain JOINED data from 1+ tables are not supported with this feature.