[SQL Server] Import
Q100064: [SQL Server] Import
Imports data to SQL Server.
| Parameter | In/Out | Description | Other Info |
|---|---|---|---|
| DB connection | In | Assign a defined SQL Server Connection or directly enter the parameters and values which make up a valid connection for SQL Server. |
Connection type: sysSqlServer |
| File source | InOut | One or more pathnames (files) containing data to import (it's common to use the FileList output variable from the [Files] Find module). |
|
| Schema Name | In | The Schema Name to be used for all new table(s) created. If not specified, it will be 'import'. Any target tables must already exist, so the schema name specified must also exist. |
Default: import |
| Table name | In | If specified, the import will go to the named table. If not specified, the table name will be based on the base file name(s) of the import file(s) (or tab names for Excel files). In either case, the table(s) must already exist. |
|
| Append import name? | In | When TRUE, the import will use the base Filename portion of the import file (or the tab name if it's a spreadsheet) and append it to the specified TableName with an underscore ("_") between them. Ex: "schemaname.TableName_MyImportFileName" or "schemaname.TableName_MyExcelTabName" | |
| Truncate table? | In | Should the target table be truncated (all data deleted) before importing? | |
| Data has a header row? | In | Does the first row of data contain column names? If there is no header row, then the columns must exactly correspond to the columns in the database, both in number and in order. |
|
| Number of rows to skip | In | How many rows to skip before reading data? This does not include the header row, so if you specify that the data has a header row, and you want to skip 5 rows, this module will read the header row, and then skip the next 5 rows. | Default: 0 |
| Log output level | In | Choices: Minimal, VerboseThe level of execution detail you want to appear in the log. |
Default: Minimal |
| Processed file list | Out | List of files that were processed. Each of these will match the parallel table names in the TableList output parameter. | |
| Processed table list | Out | List of tables that were processed. Each of these will match the parallel files in the FileList output parameter. | |
| Execution output | Out | Text returned by PRINT statements in SQL being executed. |
(Italics = required parameters)
Notes
The [SQL Server] Import module is built to import information into a SQL Server database from various file based sources. The parameter DB Connection is a SQL Server Connection which is defined for communicating with the desired SQL Server database. The File source parameter is a list of one or more files containing the data that is to be imported to the database server. The Namespace parameter is optional, but if not specified, will be treated as if set to a default value of import. The namespace is used to target which tables the input files will use. Target tables and namespaces must already exist in the destination database.
Specifying a table name via the Table name parameter will direct the data from all imported files to it when the Append import name parameter is set to false. If it is set to true, then the filename will be appended to this value and the target table name would be expected to look something like the examples in the following table.
| Exmaple |
|---|
| namespace.Tablename_ImportFileName |
| namespace.Tablename_ExcelTabName |
If the Truncate table parameter is set to true, then before the import starts, then the target table(s) will be truncated of any old data. If the import files are of a type that might include the field names as a header row in the file, then you would want to set the Data has a header row parameter to true. If the import file has extraneous information, blank lines or records, at the start of the data file, which might interfere with a successful import, then you can specify that the import process should skip a certain number of line by specifying a value for the Number of rows to skip parameter.