Add a linked data source

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.

Data source with records 1-5 merged with data source with records 6-10

The Products table containing products 1-5 and Products table containing products 6-10 have exactly the same fields. Merging the two tables creates a Products table containing products 1-10.

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.

Two data sources that are dissimilar are joined into a larger data source including all of the fields in both data sources associated by a common field.

The Products table and the Categories table are joined into a larger table that includes all of the fields in both tables, as associated by the CategoryID field.

Top of Page Top of PageTop of Page Top of Page

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:

  1. Click Data Sources in the Navigation Pane.
  2. On the Data Sources tab, in the New group, click Linked Data Source.
  3. In the Data Source Properties dialog box, on the Source tab, click Configure Linked Source.
  4. 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.
  5. Click Next.
  6. 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.
  7. Click Finish.

Both data sources, as well as the link type that you chose, appear in the Data Source Properties dialog box.

  1. In the Data Source Properties dialog box, click the General tab.
  2. In the Name box, type a new name for your data source.
  3. Click OK.

In the Data Sources list, under Linked sources, you can see your new linked data source.

Top of Page Top of PageTop of Page Top of Page

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:

  1. Click Data Sources in the Navigation Pane.
  2. On the Data Sources tab, in the New group, click Linked Data Source.
  3. In the Data Source Properties dialog box, on the Source tab, click Configure Linked Source.
  4. 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.

First page of Link Data Sources Wizard showing available data sources

 Note   You can combine two or more data sources to create a single linked data source.

  1. Click Next.
  2. 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.
  3. 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.

Page in Data Source Wizard that appears only when you link two tables in the same database

  1. 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.

Page in Link Data Sources Wizard showing available and displayed fields

To add a field to the data source display, click the field in the Available Fields list, and then click Add.

Page in Link Data Sources Wizard showing available and displayed fields

  1. Click Finish. Both data sources, as well as the link type that you chose, appear in the Data Source Properties dialog box.
  2. In the Data Source Properties dialog box, click the General tab.
  3. In the Name box, type a new name for your data source.
  4. Click OK.

In the Data Sources list, under Linked sources, you can see your new linked data source.

Top of Page Top of Page

 
 
Applies to:
SharePoint Designer 2010