Merging two SharePoint tenants' lists into one database

22 October 2026

Tags: Relational databasesMigrationDesktopCommand line

Contoso bought Fabrikam in the summer, and the integration plan says the tenant migration happens in the second quarter of next year. Until then Fabrikam keeps its own tenant, its own SharePoint sites and its own way of tracking work. The finance director does not want to wait that long. She wants one list of every open project across both companies in this month’s board pack, with the manager’s name against each line and the client it belongs to.

You are the person in Contoso IT who gets that request. Contoso tracks projects in a Projects list on its PMO site, with a Client lookup and a Project Manager person column. Fabrikam has a Jobs list on its operations site that went past the 5,000-item view threshold last year, with its own Client lookup and an Account Manager column. Someone has already tried a Power BI report with two SharePoint list sources and an append in Power Query. It worked on one laptop, signed in to both tenants, and was painful to refresh on a schedule. A database copy of both lists gets you the report without moving a single site, and it keeps working while the migration is planned around it.

Reporting across two SharePoint tenants after an acquisition

Two tenants are two separate directories. A Contoso account sees nothing on Fabrikam’s sites unless Fabrikam invites it as a guest, and an app consented in Contoso can read nothing in Fabrikam until Fabrikam’s admin grants it there as well. Whatever reads both tenants needs two sets of credentials and two sets of permissions. Microsoft also counts SharePoint’s throttling limits per app per tenant (Avoid getting throttled or blocked in SharePoint Online, Microsoft Learn), so each tenant has its own budget and a heavy read in one leaves the other alone.

SPSync works with that split. You give it one connection per tenant. It reads both through Microsoft Graph and writes into a database you own, PostgreSQL or SQL Server in this post, and it never writes back to SharePoint. Each tenant’s lists become their own tables with a prefix that says where they came from, and the report reads a view you write over the top.

One connection and one Entra grant per tenant

In the desktop app a connection is one tenant. Open the Connect screen, add a connection for Contoso, then add a second one for Fabrikam. Each connection signs in on its own and keeps its own token. A replication pairs one connection with one destination, and nothing stops two replications from writing to the same destination. You end up with a Contoso replication and a Fabrikam replication writing into the same database.

A board report that refreshes overnight needs application permissions on both sides, so syncing carries on when nobody is signed in. That means a grant in each tenant, made by that tenant’s admin. SPSync asks for the narrowest grant that does the job, which is Sites.Selected for a site or Lists.SelectedOperations.Selected for a single list. The per-list grant breaks that list’s permission inheritance, and the helper warns you before it runs. Contoso’s admin grants read on the PMO site and Fabrikam’s admin grants read on the operations site. Neither tenant grants anything tenant-wide.

In an acquisition the second admin is usually the slow part. Fabrikam’s IT team does not report to you yet, and they have no reason to trust an app they have never heard of. On the Connect screen, the Admin permissions pack card for the Fabrikam connection generates a document in Markdown or PDF with Fabrikam’s tenant name, the app’s client id, the sites and lists you picked and grant commands ready to paste. Send them that and the request is about read access to one site, which is an easy thing to say yes to. You can use SPSync’s multi-tenant app in both tenants or register your own app in each one; Admin permissions covers both routes.

From the command line you write one config per tenant. App-only sign-in needs the tenant named, and each config reads its own credential from an environment variable:

spsync export-config --output contoso.yaml --pipeline-name contoso \
  --prefix cto --destination-kind postgres \
  --connection-string-env SPSYNC_DSN --dataset-name sharepoint \
  --tenant contoso.onmicrosoft.com --client-id $CONTOSO_CLIENT_ID \
  --client-secret-env CONTOSO_CLIENT_SECRET \
  --site-url https://contoso.sharepoint.com/sites/pmo \
  --lists-by-name Projects,Clients

spsync export-config --output fabrikam.yaml --pipeline-name fabrikam \
  --prefix fab --destination-kind postgres \
  --connection-string-env SPSYNC_DSN --dataset-name sharepoint \
  --tenant fabrikam.onmicrosoft.com --client-id $FABRIKAM_CLIENT_ID \
  --client-secret-env FABRIKAM_CLIENT_SECRET \
  --site-url https://fabrikam.sharepoint.com/sites/operations \
  --lists-by-name Jobs,Clients

The two files share a connection string and a dataset. They differ in tenant, credential, pipeline name and prefix. A certificate works in place of a client secret if your security team prefers one. Run spsync run contoso.yaml and spsync run fabrikam.yaml from whatever scheduler you already use; the automation guide covers cron, Task Scheduler and CI runners.

Table prefixes per tenant in one database

Every list becomes a table called {prefix}_{list}. The configs above give you cto_projects, cto_clients, fab_jobs and fab_clients in the sharepoint schema. Both companies have a Clients list, and the prefix is what keeps them apart. A prefix may contain lowercase letters, digits and _, and a config with anything else is refused when it loads, with the corrected prefix suggested in the error.

Choose the tenant codes before the first run and keep them. With a short code per tenant at the front of every table name, the first question anyone asks of a merged database, whose data is this, has an answer before they open a table. If Fabrikam has several sites, put the tenant code first and the site after it. In a CLI config, naming.prefix_template set to "{prefix}_{scope_prefix}" gives fab_ops_jobs and fab_sales_jobs from one Fabrikam replication.

In the desktop app, the naming step of the New replication wizard takes a prefix per site and previews the table names it will create. The preview looks at one replication at a time, so it cannot see the other tenant’s tables. Starting every Fabrikam prefix with fab and every Contoso prefix with cto keeps them from meeting.

How do people columns work across two tenants?

A person column becomes {column}_user_id on the row. The person’s name and email live in a shared sp_users table with id, login, display_name, email and is_group. The created-by and modified-by columns on every row, sp_author_id and sp_editor_id, point at the same table. There is one sp_users per destination schema and it has no prefix, so with both configs writing to sharepoint, Contoso’s people and Fabrikam’s people sit side by side in it.

That works because of how the id is made. SPSync derives sp_users.id from the person’s login, which is their lowercased UPN. A Contoso UPN ends in a Contoso domain and a Fabrikam UPN in a Fabrikam one, so the two tenants’ people can never collide. The same rule means one human can appear twice. Someone with an account in each tenant has two logins, and so does a Contoso employee who is a guest in Fabrikam. SPSync records the logins it sees and leaves the question of who is the same person to you. Most merger reports need that answer anyway, and a small mapping table you own, filled from HR’s records or matched on email, settles it in one place.

If you would rather each tenant kept its own sp_users, give the two CLI configs different --dataset-name values. Each tenant then gets a schema of its own, and the view names both of them. The desktop app writes every replication on a destination into one schema, so there the table is shared.

SharePoint lookup columns stay inside their own tenant

A SharePoint lookup points at another list on the same site, so it can never reach across tenants. Graph gives SPSync the target item’s id, and SPSync writes it as {column}_id, a key that joins to the target table’s sp_item_id. On Fabrikam’s Jobs list the Client lookup becomes client_id, which joins to fab_clients.sp_item_id.

sp_item_id is SharePoint’s item number and it is only unique within its own list. Contoso’s Clients list will have an item 42, and so will Fabrikam’s. While each lookup is joined to its own tenant’s tables that does no harm. It gives you the one rule for the merged view: resolve each tenant’s lookups inside that tenant’s half, then stack the halves. A join from fab_jobs.client_id to cto_clients would run without an error and put the wrong client on every line.

A merged reporting view across both tenants

Contoso’s list is Projects, with a Status choice and a Project Manager. Fabrikam’s is Jobs, with a Stage choice and an Account Manager. The view maps both into one shape and adds a column naming the tenant. In PostgreSQL it looks like this:

create schema if not exists reporting;

create or replace view reporting.open_projects as
select 'Contoso'        as tenant,
       p.sp_site_id,
       p.sp_item_id,
       p.title          as project,
       c.title          as client,
       u.display_name   as manager,
       p.status         as status,
       p.sp_modified,
       p.sp_synced_at
from sharepoint.cto_projects p
left join sharepoint.cto_clients c on c.sp_item_id = p.client_id
left join sharepoint.sp_users u    on u.id = p.project_manager_user_id
where p.sp_deleted_at is null
  and p.status <> 'Closed'

union all

select 'Fabrikam',
       j.sp_site_id,
       j.sp_item_id,
       j.title,
       c.title,
       u.display_name,
       j.stage,
       j.sp_modified,
       j.sp_synced_at
from sharepoint.fab_jobs j
left join sharepoint.fab_clients c on c.sp_item_id = j.client_id
left join sharepoint.sp_users u    on u.id = j.account_manager_user_id
where j.sp_deleted_at is null
  and j.stage not in ('Complete', 'Cancelled');

On SQL Server the view is the same apart from create or alter view and how the schema is created. Power BI then connects to reporting.open_projects through the PostgreSQL or SQL Server connector, by import or DirectQuery, and both tenants arrive in the model as one table. A row is identified by tenant and sp_item_id together.

The harder work in that view is the business mapping. Contoso’s “Active” and Fabrikam’s “In progress” may or may not mean the same thing, and finance should decide that before it goes in the where clause. SPSync can also generate a view per list, such as v_fab_jobs, with person columns already joined to names and deleted rows left out. Turn it on with “Create reporting views” in the desktop app or --create-reporting-views on the command line. Those views cover one list each, so the cross-tenant view is still the one you write.

When someone adds a column to the Jobs list, it appears in fab_jobs on the next run, and existing rows get it as null. The view lists its columns by name, so it carries on working, and the new column reaches the report when you add it to the view.

Which tenant did this row come from?

Somebody in finance or audit will ask where a number came from, usually the week after the board meeting. Every replicated row carries the answer. sp_site_id is Graph’s composite site id, which starts with the SharePoint host name, so a Fabrikam row begins fabrikam.sharepoint.com, and a Contoso row contoso.sharepoint.com,. sp_list_id names the list. sp_modified is when the item last changed in SharePoint and sp_synced_at is when SPSync last wrote it. A count per host shows both tenants in the view and how fresh each one is:

select split_part(sp_site_id, ',', 1) as sharepoint_host,
       count(*)                      as open_projects,
       max(sp_synced_at)             as last_written
from reporting.open_projects
group by 1;

Deletes are kept by default. An item someone deletes in Fabrikam’s tenant keeps its row with sp_deleted_at set, so the copy still shows the project that vanished from the Jobs list last Tuesday, and when it went. The view leaves deleted rows out, and a second query without that filter finds them. The desktop app’s Runs screen lists each replication’s runs, and on the command line the scheduler’s own logs hold the run output.

Checking both copies before the board pack goes out

After the first full load, each run reads only what changed since the last one, through Graph’s delta query. Each replication also runs a reconcile on its own schedule, weekly by default, which re-checks the whole copy against SharePoint. You can run it on demand, and the morning the board pack is built is a good time:

spsync reconcile contoso.yaml
spsync reconcile fabrikam.yaml

Treat it as the QA step before the numbers leave IT, and note in the pack that both copies were checked against their tenants that morning. The same command catches a copy up after an outage on your side or Microsoft’s. If the server that runs the schedule was off over a weekend, a reconcile on Monday brings both tenants’ tables back in step before anyone opens the report.

When the tenant migration happens

SPSync does not move sites between tenants. Microsoft’s cross-tenant migration tooling or a migration product does that. What the Fabrikam replication gives you on migration day is a record of what Fabrikam’s lists held before anything moved. Keep the fab_ tables, replicate the migrated sites from Contoso’s tenant under a new prefix, and compare the two copies to confirm the move. Compare on a business key such as a project code, because the migration tool decides what happens to item ids. Keeping a copy through tenant migrations and mergers walks through that before and after check.

Once the move is confirmed, point the Fabrikam half of the view at the new tables and stop the Fabrikam replication before its tenant is switched off. The board report keeps its shape the whole way through.

When you do not need a database for this

If both lists are small and the report is one page refreshed by hand, two SharePoint list sources in Power BI with an append in Power Query will do. It slows down as the lists grow, for reasons covered in why Power BI refreshes on SharePoint lists slow down.

If the migration is a few weeks away and the joined report can wait until it is done, waiting is cheaper. Report from one tenant after the move and skip the second connection.

A database copy earns its place when the report has to run on a schedule for months or when either list is large. It pays off again once lookups and people have to show as names, and once more when a second report starts reading the same tables.