import-data-from-tables-master-data-services.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic
Import Data from Tables	Import data from tables and make data changes to a model, in bulk. Use this procedure to add, update, and delete data in the Master Data Services database.	CordeliaGrey	jiwang6	03/14/2017	sql	master-data-services	conceptual

Import Data from Tables (Master Data Services)

[!INCLUDE SQL Server - Windows only ASDBMI]

You can add data and make data changes to a model in [!INCLUDEssMDSshort], in bulk.

Prerequisites

• You must have permission to insert data into the stg.<name>_Leaf, the stg.<name>_Consolidated, stg.<name>_Relationship table in the [!INCLUDEssMDSshort] database.

• You must have permissions to execute either the stg.udp_<name>_Leaf, stg.udp_<name>_Consolidated, or the stg.udp_<name>_Relationship stored procedure in the [!INCLUDEssMDSshort] database.

• The model must not have a status of Committed.

To add, update, and delete data in the [!INCLUDEssMDSshort] database

1. Prepare the members for import into the appropriate staging table in the [!INCLUDEssMDSshort] database, including providing values for the required fields. For an overview of staging tables, see Overview: Importing Data from Tables (Master Data Services)

   • For leaf members the table is stg.<name>_Leaf, where <name> refers to the corresponding entity. For information about the required fields, see Leaf Member Staging Table (Master Data Services)

   • For consolidated members, the table is stg.<name>_Consolidated. For information about the required fields, see Consolidated Member Staging Table (Master Data Services).

   • For moving the location of members in explicit hierarchies, the table is stg.<name>_Relationship. For information about the required fields, see Relationship Staging Table (Master Data Services).

     For an overview on moving members in explicit hierarchies, see Overview: Importing Data from Tables (Master Data Services).

   • Use the ImportType field value to specify that you're creating new members, deactivating members, or deleting members. For more information about the values, see Leaf Member Staging Table (Master Data Services) and Consolidated Member Staging Table (Master Data Services).

     For an overview of deactivating and deleting members, see Overview: Importing Data from Tables (Master Data Services).

2. Open [!INCLUDEssManStudioFull] and connect to the Database Engine instance for your [!INCLUDEssMDSshort] database.

   For more information, see SQL Server Management Studio.

3. Import data into the staging tables by using the [!INCLUDEssNoVersion] Import and Export wizard.

   For more information, see SQL Server Import and Export Wizard.

4. Load the data from the staging tables to the [!INCLUDEssMDSshort] tables, by doing one of the following

   • Run the staging stored procedure that corresponds to the staging table that you want to move data to.

     For an overview of staging stored procedures and staging tables, see Overview: Importing Data from Tables (Master Data Services). For more information about parameters for staging stored procedures, and a code example, see Staging Stored Procedure (Master Data Services).

   • Use the Integration Management functional area of Master Data Management.

     On the Staging Batches page, select the model to which you're adding data to, in the drop-down list, and then click Start Batches. The status of the batch processing is indicated in the Status field. For more information about the statuses, see Import Statuses (Master Data Services).

     

     The staging process is started at intervals determined by the Staging batch interval setting in [!INCLUDEssMDScfgmgr]. For more information, see System Settings (Master Data Services).

5. View errors that occurred during staging. For more information, see View Errors that Occur During Staging (Master Data Services) and Staging Process Errors (Master Data Services).

6. Validate the data against business rules.

   In Master Data Manager, navigate to the Explorer functional area for your model, and then apply business rules to validate the data. For more information , see Validate Specific Members against Business Rules (Master Data Services). You can also use a stored procedure to validate the data. For more information, see Validation Stored Procedure (Master Data Services).

   When you load data by from the staging tables, the data is not automatically validated against business rules. For more information on what validation is and when it occurs, see Validation (Master Data Services).