Export to Database function is used to update/insert/replace data in an existing table in the database.
The following guidelines describe how Export to Database is used.
Select the data that needs to be exported. There are two ways by which you can select the data: 1)By manually selecting the data from the sheet. 2)By entering the cell indices of the starting and ending block, for example $A$1:$I$800.
You can select Change sheet if you want to select data from some other sheet. Also, click on the My data has headers checkbox if you have selected the headers with the data.
After selecting the data, click on OK.
Click on the browse button next to Connection, to configure the database connection. If the Configuration File is already setup as explained in Desktop & Server Configurations, then this step can be skipped.
Select a database system from the Database Drop-Down box and the fields corresponding to the particular database system appears. For example, if SQL Server database is selected, the fields Data Source, Integrated Security, User ID, Password and Initial Catalog appears. You can enter these fields individually or a connection string can be entered in the Connection String field. If you are entering the fields individually the connection string gets updated automatically.
Under Columns in Range, the column names in the data range will be listed. And corresponding to each column there is a checkbox and a dropdown window. If a particular column is needed to be exported to database the checkbox should be checked. The dropdown window contains all the columns in the database. For each column in the data range to be exported, select the corresponding column in database. If the name of the database column and the data range column is the same, the columns will be mapped automatically when the database is selected. Here only the column "Country of Incorporation" is not mapped to a database column, since the names mismatch. In such cases, if the column needs to be exported, the database column is selected manually and the the checkbox is also checked.
Under Columns in Database, the column names in the database will be listed. And corresponding to each column there are two checkboxes: Output and Key. If a particular column is the primary key then Key checkbox should be checked. If a particular column need to be reported as a part of the result, the Output checkbox should be checked.
|UpdateOrInsert||Updates the database rows in which the Primary Keys are matching with the data rows and inserts new rows into the database table if the data rows don't have matching Primary Keys. The existing rows in the database which don't match with the data rows will be retained.|
|TableReplace||Replaces the entire database table data with the new data.|
|BatchReplace||Replaces the database rows that matches with Batch Key.|
|InsertAll||Inserts new rows into the database table if the data rows don't have matching Primary Keys. But if there are the database rows for which the Primary Keys are matching with the data rows, operation fails with error.|
|InsertNew||Similar to InsertAll. But ignores if there are the database rows for which the Primary Keys are matching with the data rows.|
|UpdateAll||Updates the database rows in which the Primary Keys are matching with the data rows. But if the data rows don't have matching Primary Keys, operation fails with an error.|
|UpdateExisting||Similar to UpdateAll. But ignores if the data rows don't have matching Primary Keys.|
Click on Next. You will be asked where to save the result. To save the data in the Existing WorkSheet, you can input the index of the starting cell where you want to copy the data. Or you can save the data in a New Worksheet.