How to merge two lists of different domains with same columns into one new list with a new column categorizing the merger?

Question

I have 2 lists:

1. Student (lies in student domain)
2. Employees (lies in employee domain)

They both have same columns.

I would like to create a new merged list which should also include a column called 'category' which would have 2 values 'Student' and 'Employee'. I will be using PowerShell. How should I achieve this?

This would categorize the merged dataset records.

Both lists are updated daily (realtime).

Also, is there a way where I can automate this merger? (so that I do not have to manually execute the script daily)

Thanks in advance.

Answer 1

Are you using SharePoint Online or SharePoint on-premises? The process would be quite different for these two products.

For SPO, it would be easier since the two domains would be on cloud, and you can use tools like Power Automate to update and sync automatically.

For SharePoint on-premises, you would need to establish connection between these two domains first. (A sample here: New-PSSession across domain boundaries.)

And to automate the process of merging the two lists, consider building an event receiver to do the daily task. See another similar post here: How to keep two SharePoint Lists in sync when located on 2 different servers.