Most enterprises store related data in multiple locations. For example, product information might be stored in one location, and information about product categories is stored in another location. With Microsoft SharePoint Designer 2010, you can easily link data sources that contain interrelated data to one another. You can even link data sources of separate types — for example, you can link an XML file to an SQL database. By linking multiple data sources, you create a single data source that appears in the Data Sources list with all of your other data sources.
After you create a linked data source, you can use it like any other data source — for example, you can create a Data View that displays data from all of the data sources in a single linked source.
In this article
Merge or join the data?
When you link data sources, you have two choices: merging the data sources or joining them.
Merge You merge data when all of the data sources that you want to merge into a single data source have exactly the same set of fields. For example, if you are linking the Products tables from several different databases, and if each table has exactly the same fields as all of the other tables, then you merge the data. In effect, you create a single longer table out of several shorter tables. Merging data is particularly useful if you want to sort, group, or filter multiple similar data sources in a single Data View.
Join You join data when two separate data sources have one field in common. For example, if you are linking a Products table with a Categories table, and the tables are linked by a field called CategoryID, then you join the data. Joining data is particularly useful if you want to present related data sources in a single Data View.
Link data sources by merging them
If you have multiple data sources, all having exactly the same fields, that you want to link as a single data source, link them by merging:
-
Click Data Sources in the Navigation Pane.
-
On the Data Sources tab, in the New group, click Linked Data Source.
-
In the Data Source Properties dialog box, on the Source tab, click Configure Linked Source.
-
In the Link Data Sources Wizard, under Available Data Sources, click your data source, and then click Add. Next, click the data source that you want to link with the first data source, and then click Add.
-
Click Next.
-
Under Select the link type that best represents the relationship between the selected sources, click Merge the contents of the data sources. Choose this option if you'd like to sort, group, and filter the sources as one long list.
-
Click Finish.
Both data sources, as well as the link type that you chose, appear in the Data Source Properties dialog box.
-
In the Data Source Properties dialog box, click the General tab.
-
In the Name box, type a new name for your data source.
-
Click OK.
In the Data Sources list, under Linked sources, you can see your new linked data source.
Link data sources by joining them
If you have two separate data sources that have a single field in common that associates one data source with the other, link them by joining:
-
Click Data Sources in the Navigation Pane.
-
On the Data Sources tab, in the New group, click Linked Data Source.
-
In the Data Source Properties dialog box, on the Source tab, click Configure Linked Source.
-
In the Link Data Sources Wizard, under Available Data Sources, click your data source, and then click Add. Next, click the data source that you want to link with the first data source, and then click Add.
Note: You can combine two or more data sources to create a single linked data source.
-
Click Next.
-
Under Select the link type that best represents the relationship between the selected sources, click Join the contents of the data sources by using the Data Source Details to insert data views and joined subviews.
-
If you are joining data sources that are not all database tables, click Finish. But if you are joining database tables such as two SQL data tables, click Next.
Important: If you are linking only two tables and they are both from the same database, you are prompted to choose the field that contains matching data in each table. If you are linking more than two tables from the same database, two tables from separate databases, or two data sources that are not database tables, skip to step 8.
If you are linking two tables from the same database, then in the Link Data Sources Wizard, choose the field from each column that contains the matching field. In this example, the matching field is CategoryID.
-
Click Next.
By default, all of the fields in both data sources are displayed.
To remove a field from the data source display, click the field in the Displayed Fields list, and then click Remove.
To add a field to the data source display, click the field in the Available Fields list, and then click Add.
-
Click Finish. Both data sources, as well as the link type that you chose, appear in the Data Source Properties dialog box.
-
In the Data Source Properties dialog box, click the General tab.
-
In the Name box, type a new name for your data source.
-
Click OK.
In the Data Sources list, under Linked sources, you can see your new linked data source.
