[SQL Server] Export
Q100059: [SQL Server] Export
Exports data from 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 |
| SQL query | In | SQL query to be executed. | |
| Result table name(s) | In | The name(s) you want to give your exported data. If exporting to Excel, this will be used to label the worksheet tab(s). If exporting to SQL, it will be used as the table name(s) in the export script. If you have more than one output set (i.e., multiple SELECT statements in your query), you can specify multiple names here, separated by commas or semicolons. For example, if your query is "SELECT * FROM dbo.ProductDetails; SELECT * FROM dbo.Employees", you might want to specify the output names as "Product Details, Employees" for Excel, so the tabs get those names. If exporting to SQL, you might specify "@ProductDetails, @Employees" to export to the temp tables as named. All of this is optional. If left blank, reasonable defaults will be used, like "Sheet 1" or "Table2". |
|
| Timeout seconds | In | Timeout in seconds for the query to execute. Default is 0 seconds, the same as SSMS, which will wait indefinitely. | Default: 0 |
| Export file type | In | Choices: Excel, JSON, XML, CSV, TabDelimited, SQLWhat type of file do you want to export to? |
|
| Target pathname | In | The fully-qualified pathname of the output file you want to create. If the query returns more than one result, there may be more than one output file, depending on the export file type you selected. Your result table names will be appended to the file name, or, if you don't specify any, they will have "_1", "_2", "_3"... appended to the file name. If there is only one result, then the file name will be used exactly as you specified it. |
|
| Exported file list | Out | List of files that were exported during the process. | |
| Log output level | In | Choices: Minimal, VerboseThe level of execution detail you want to appear in the log. |
Default: Minimal |
| Execution output | Out | Text returned by PRINT statements in SQL being executed. |
(Italics = required parameters)
Notes
The [SQL Server] Export module is built to export information via a SQL query. The parameter DB Connection is a SQL Server Connection which is defined for communicating with the desired SQL Server database. The SQL query parameter is a standard T-SQL query that you execute on the database server. In this module, the query is expected to return one or more result sets. Each result set that is returned is processed in order and the values in the list of names in the parameter Result table name(s) is expected to be in the same ordinal position. Thus if there are three names in this parameter, then each name should correspond 1:1 with three result sets returned by your SQL query.
The parameter Export file type is used to determine the output format of the data returned by each result set in the query. The valid options are Excel, JSON, XML, CSV, TabDelimited, and SQL. These formats are all mostly self explanatory, but are outlined in the table below.
| Export file type | Output format description |
|---|---|
| Excel | The output file will be a single Excel (.xlsx) file, with each result set output to a separate tab within the Excel file. |
| JSON | The output file(s) will be a block of JSON data that corresponds to the columns defined in the query. |
| XML | The output file(s) will be a block of XML data that corresponds to the columns defined in the query. |
| CSV | The output file(s) will be a block of CSV data that corresponds to the columns defined in the query. |
| TabDelimited | The output file(s) will be a block of Tab Delimited data that corresponds to the columns defined in the query. |
| SQL | The output file(s) will be a set of SQL insert statements that could be executed on another server directly to get the data into the same basic schema. |
The parameter Target files path should look like a fully qualified path and filename, such as T:\Temp\MyDataResults.xlsx. If you are using the Excel export file type, then all the result sets will be created as tabs within a file with this exact name. The SQL export file type also generates a single output file, but uses the names specified in Result table name(s) as the table names for each returned result set. Or if Result table name(s) is not speified for the SQL type, then it will just use the generic names, Table1, Table2, Table3 and so on for all results in the query.
The other export file types will behave slightly differently from the Excel and SQL types. The remaining types all will treat the target files path value as a base template for multiple files it can generate if the query returns multiple result sets. For example, if the target file path is T:\Temp\MyDataResults.json and you have set the Export file type to JSON, then each result set that is returned will be written to a unique file. The exact filename will depend on if you supplied the corresponding values in the Result table name(s) parameter or not. If your query returned three result sets, and you did not specify any values for Result table name(s), then the file names generated would look something like the following table.
| Output path and filename |
|---|
| T:\Temp\MyDataResults_1.json |
| T:\Temp\MyDataResults_2.json |
| T:\Temp\MyDataResults_3.json |
However, if you did supply a list of corresponding names for each of the result sets in the Result table name(s) parameter, then the output would look different.
| Ordinal value in Result table name(s) | Output path and filename |
|---|---|
| ProductDetails | T:\Temp\MyDataResults_ProductDetails.json |
| Employees | T:\Temp\MyDataResults_Employees.json |
| Orders | T:\Temp\MyDataResults_Orders.json |
The other export file types behave in the same manner as the JSON type, as for how the Target file path parameter is treated as a filename template.