Sync Microsoft SQL Server to Anvil.works on a Schedule

Automation · SQL Server to Anvil.works

Keep Anvil.works in step with your SQL Server database, on a schedule

Write the SQL once. Query Streams runs it on your schedule, anything from every five minutes to weekly, and writes the rows into your Anvil.works data table. Match rows by a key so edits land in place and rows that left the query are removed, or simply append. No Zapier, no scripts, no exports. About fifteen minutes the first time, and the Free plan covers it.

About 15 minutes No coding needed Data stays on your network Every 5 minutes to weekly

What you’ll end up with

An Anvil.works data table that holds the rows your SQL Server query returns and is brought up to date on a schedule, every five minutes to weekly, with a receipt of what changed on every run. Like this:

Orders to Anvil.works Active

Daily sync · 09:00 · Anvil.works 1,643 rows · 8.8s

1,627rows in syncmatched by key · nothing to do

11newappended under your headers, same columns

5updatedprices changed in SQL Server, 5 rows updated in place

0removedrows leaving the query are removed from the data table

Microsoft SQL Server holds your data on a server you or your team run. Your team works in Anvil.works. The usual bridge is a hand-written import in your app that breaks quietly and copies one row at a time. Query Streams Automation does it differently: the Agent that already sits with your SQL Server database runs your saved SQL on a schedule and writes the whole result into Anvil.works in one pass, matching rows by a key so the data table stays correct rather than growing.

What you’ll need

A Query Streams account

The Free plan is enough for this guide. Sign up at my.querystreams.com.

SQL Server connected through the Agent

The Agent installed where your SQL Server database is, with the connection added in Data Agents. Not done yet? Connect SQL Server first; it takes about ten minutes.

A saved query

The SELECT you want to keep in sync, saved in Query Builder. Step 1 below shows one. If you want changed rows updated in place, include a column that is unique per row, like an ID.

Your Anvil.works login

Enter the HTTPS address of your Anvil app and the connector key you set in the app with connector.init_connector("key") from the querystreams module. Query Streams checks the app answers and lists its data tables.

Set it up in six steps

1. Save the query you want to keep in sync

   Open Query Builder, pick your SQL Server connection (not connected yet? start here), and write the SELECT that returns exactly the rows and columns you want in Anvil.works. This one pulls a few columns from a Customers table:

   Example · SQL

   SELECT TOP 100 CustomerID, CompanyName, City
   FROM dbo.Customers
   ORDER BY CompanyName;

   Run it, then Save it with a name you will recognise, like “Orders for Anvil.works”. Keep a column that is unique per row in the SELECT, here CustomerID. That is the key Full Sync uses to update rows in place. Edit and re-save the query later and the automation picks up the change on its next run.

2. Open Automation and choose Anvil.works

   In the portal, open Automation (top menu Streams → Scheduled → Automation) and click New automation. The wizard has five short steps. On Step 1 click the Anvil.works card.

   Open Automation

   Seeing “Before You Begin” instead? The wizard only opens once an Agent is online with a connection and at least one saved query exists. The page lists which of the three is missing and links to it.

3. Connect Anvil.works

   On Step 2 the page walks you through the Anvil side: add the Query Streams dependency code to your app under Settings → Dependencies → Third Party, create a Server Module with from querystreams import connector and connector.init_connector("your key"), and click Generate Secure Key to make that key. Then enter your Anvil App URL and the same Connector Auth Key and click Test Connection. Your app needs Accelerated Tables switched on (Settings → Data Tables); the page re-checks that for you.

4. Choose where the rows land

   On Step 3 pick the Data Table the rows should land in. Create it in your Anvil app first if you want an empty one. If it already has rows, a preview shows them and explains what each sync mode would do to them. One automation per table.

5. Pick the query and decide how rows land

   On Step 4 open the Saved Query list and choose the query from step 1. Query Streams runs it once to read the columns, tells you how many rows it returns, and shows two choices:

   Append · fast

   Append

   Every run adds the query’s rows to the bottom of the data table. Nothing is updated and nothing is deleted, so it is the quick choice for logs, events and history you want to accumulate. No key column needed. A row that comes back unchanged is not added again; a row whose values changed is added below as a new line and the old one stays.

   Full Sync · mirrors

   Full Sync

   Rows are matched by a key column such as an order ID. New rows are added, changed rows are updated in place, and rows that no longer come back from the query are removed, including rows someone typed into the data table by hand, so keep hand-made rows on another data table. This is the choice for inventory, pipelines, master data and dashboards.

   Full Sync is set per column, which is what makes it useful in a shared data table:

   Key

   The column that identifies a row, for example order_id. It has to be unique in the query result; Query Streams checks that for you and refuses duplicates.

   Sync

   Overwritten from SQL Server on every run. If someone edits a Sync column in Anvil.works, the next run puts the source value back.

   Preserve

   Written when the row first arrives and never compared again, so a change to it in SQL Server does not trigger an update. For notes, owners and follow-up dates your team types by hand, use a column that is not in the query at all: columns that exist only in Anvil.works are never touched by a sync.

   Which is faster? Append writes without reading the data table first. Full Sync reads it, compares every row, and writes only the differences, so it takes longer but the data table always matches the query. You can switch later from the automation’s Edit panel.

   For a data table you want to trust, click Create all columns as Sync, then click the key icon on CustomerID to make it the Key. Query Streams scans the whole result to confirm the key is unique and warns you if it is not. Keep your team’s own notes in columns that are not part of the query; those are never touched. For a log that should only ever grow, click Create all columns as Preserve instead and skip the key. Click Continue.

6. Set the schedule, name it, create it

   On Step 5 choose Run on a schedule and pick a Frequency:

   Frequency	Runs
   Every 5 Minutes	every 5 minutes, the fastest allowed
   Every 15 Minutes	every 15 minutes
   Every 30 Minutes	every 30 minutes
   Every Hour	at the top of every hour
   Daily at 9 AM	every day at 9:00, the default
   Daily at 6 AM	every day at 6:00
   Weekdays at 9 AM	Monday to Friday at 9:00
   Weekly (Monday 9 AM)	every Monday at 9:00
   Custom Schedule	your own minutes, hours, days of the week or day of the month

   Times follow the Timezone you pick, which starts as your browser’s. You can also choose Manual only and press Run whenever you like; a schedule can be added later from the automation’s Edit panel. Scheduled runs happen on the Agent, so they keep going while your browser is closed. If the computer running the Agent is off when a run is due, the missed run happens once, shortly after the Agent starts again.

   Give it an Integration Name, such as “Orders to Anvil.works, daily”, check the Ready to Create summary and click Create Automation. Back on the Automation page, press Run once to do the first sync now rather than waiting for the schedule. Open Anvil.works: the rows are there, under your column headers.

What you see afterwards

The Automation page lists every automation with its status, source, target, key, schedule, last sync and row count. Open one for the Executions tab: every run with its trigger (manual or scheduled), duration, and rows created, updated, deleted and unchanged. A Performance tab shows data processed and success rate over 7 to 90 days.

What the plans allow

Automation is on every plan, including Free, with no limit on how many automations you create. What each plan limits is how many rows one query may return per run:

If a query returns more, the run is held rather than cut short, and the automation tells you which plan fits or suggests adding a filter such as a date range. Each successful run also counts against your Data Realm allowance, the same pool your Excel and Sheets queries use. See the pricing page for the allowances.

Good next moves

• Add a filter to the query, such as the last 90 days, so the data table stays small and fast and fits your plan’s row limit.
• Keep hand-typed notes in their own columns, outside the query, so they survive every sync.
• Group your automations from the Edit panel once you have a few; the Automation page filters by group.

Questions people ask

Related guides

• MySQL to Anvil.works
• PostgreSQL to Anvil.works
• SQLite to Anvil.works
• Microsoft SQL Server to Google Sheets
• Microsoft SQL Server to Airtable
• How to connect Microsoft SQL Server to Google Sheets
• All Anvil.works automation guides