The same list on many sites into one table

Last updated 23 October 2026

The same list template is copied across dozens of site collections, and there is no single place to report on all of them together without exporting each one by hand.

Forty project sites, all created from one template, means forty copies of the same Risk Register list. When the board wants the top risks across the portfolio, someone opens each site, exports the list and pastes it into a spreadsheet that is out of date before the meeting starts. SharePoint’s content rollups and search help little here: they were not built for joins across that many lists, and they return SharePoint’s item shape.

One database, forty prefixes

SPSync replicates the same list from many sites into one destination database. Each site gets its own table prefix, so a row always says which site it came from, and each site syncs on its own schedule. Whole-site selection brings a site’s related lists in together, so the register arrives with the lookup lists it points at.

Inside each prefix the tables have the shape a single-site sync produces: lookups as key columns, multi-value columns as child tables, and people resolved into the shared sp_users table. The portfolio report is a UNION ALL across the per-site tables, written once as a view so everyone downstream queries one name.

Forty configs from one file

Setting up forty sites by hand is the chore this use case removes. Browse or search the tenant for the sites that carry the list, then write a batch definition that names them, or names the whole site collection. spsync batch create turns that definition into one replication config per site, all pointing at the same database, each with its own prefix. A new project site next quarter is one more line in the batch file.

Questions the board will ask

Historical totals add up. When a risk is deleted from a site’s register, its row stays in that site’s table with sp_deleted_at set, so last quarter’s count still matches. And the consolidated report needs one destination database however many sites feed it; the prefixes keep the sources apart once everything sits in one place.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name consolidate_multiple_sites --prefix consolidate_multiple_sites --destination-kind postgres --connection-string-env SPSYNC_DB_CONNECTION --dataset-name consolidate_multiple_sites --client-id 00000000-0000-0000-0000-000000000000 --site-url https://contoso.sharepoint.com/sites/example --all-lists
spsync doctor replication.yaml
spsync run replication.yaml

pipeline_name: consolidate_multiple_sites
prefix: consolidate_multiple_sites
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: postgres
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: consolidate_multiple_sites
scopes:
  - site_url: https://contoso.sharepoint.com/sites/example
    mode: entire_site
reconcile_interval_days: 7
create_reporting_views: false

# every night at 02:00
0 2 * * * cd /opt/spsync && SPSYNC_DB_CONNECTION="$(cat /etc/spsync/db.env)" uv run spsync run replication.yaml >> /var/log/spsync/run.log 2>&1

# .github/workflows/spsync-run.yml
name: SPSync
on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch: {}
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v3
      - run: uv sync
      - run: uv run spsync run replication.yaml
        env:
          SPSYNC_DB_CONNECTION: ${{ secrets.SPSYNC_DB_CONNECTION }}
          SPSYNC_LICENCE_KEY: ${{ secrets.SPSYNC_LICENCE_KEY }}

# azure-pipelines.yml
schedules:
  - cron: "0 2 * * *"
    branches: { include: [main] }
    always: true
steps:
  - script: uv sync
  - script: uv run spsync run replication.yaml
    env:
      SPSYNC_DB_CONNECTION: $(SPSYNC_DB_CONNECTION)
      SPSYNC_LICENCE_KEY: $(SPSYNC_LICENCE_KEY)

# Windows Task Scheduler action (see /docs/cli/automation/)
$env:SPSYNC_DB_CONNECTION = (Get-Content C:\spsync\db.env)
uv run spsync run C:\spsync\replication.yaml
schtasks /Create /SC DAILY /ST 02:00 /TN "SPSync" /TR "powershell -File C:\spsync\run.ps1"

# ~/Library/LaunchAgents/io.spsync.run.plist, then: launchctl load ~/Library/LaunchAgents/io.spsync.run.plist