Hi Automate Community!
This week's tip is about extracting data to SQL Server using Query. Please note that this applies for Studio with Evolve or Studio Manager only.
Most Query users will be familiar with extracting data from SAP to Excel, but there are several other output formats available such as Access, txt, XML or SQL Server. Extracting to SQL Server can be particularly useful in integration scenarios with other platforms or tools, for data analytics or simply to bypass the 1,000,000 row limit in Excel.
To be able to use SQL Server as an output format in Studio, the SQL server and database need to be configured by the admin in the Connections – Data Sources page of your Studio Manager or Evolve site:

This list of SQL servers and databases determines the available databases you can extract to once you create the query. As an end user, you won't have to do this configuration, but for reference, you can find more information in the user guide here.
Creating a query mapped to SQL Server is much like creating a query mapped to excel; the only steps that will differ will be in the Mapper tab.
When you start creating the query, you will be prompted to select a data type/output format. You can select SQL Server at this point, or you can also start with Excel and switch the data type to SQL Server further on. I usually build and do all my testing using Excel and map to SQL Server at the end but this is purely a matter of personal preference:

Once you've built the query and tested using the Workspace, Criteria and Run Time tabs, you can head to the Mapper tab to map your fields to a table in a SQL Server Database. At the bottom left of the screen you can see a dropdown showing that SQL Server is selected as the output format. If you had initially selected Excel, this is where you would switch to SQL Server. You will also see that Studio is proposing a table name, which you can change at this moment or when selecting the SQL Server database you want to use.

Next, use the folder icon to open the dialog to select the database.
The Connection Name dropdown shows the list of SQL Servers available in the Connections – Data Sources of Studio Manager/Evolve. Under Select Table you can choose an existing table or create a new one. In my example I'm creating a new SQL table named "DV_FIDocuments", which I plan to fill with accounting document data from SAP.

Mapping the fields is slightly different to mapping to Excel; you can drag each individual field from the top to the bottom of the screen to create columns in your table, but once the field is mapped it can't be remapped to a different column. If you're halfway through mapping your fields and decide you need to change the order of the columns, the easiest thing to do is to click on the folder icon again and recreate the table with the same name, effectively resetting the mapping. Another suggestion is to use the Shift Up and Shift Down buttons on the top left to change the order of the fields and then use the Auto Map button.
Once you've mapped all the columns you should see a similar result to this, where each SAP field is mapped to a column of the SQL table:

At this point you are ready to run the query. The first time you run, the table ("DV_FIDocuments" in my example) will be created in the SQL Server database and filled with data from your query. Subsequent runs will fill data to the same table.

In my example, I am using the Append Data to Result File option in Query, and using filter criteria to select only accounting documents created by particular users on today's date. That way, the documents posted to SAP today will be appended to my SQL table instead of them replacing already existing rows. I can then schedule this query to run automatically so that every day my SQL table is updated with only the accounting documents posted into SAP that day.
Scheduling runs was covered in the past by my colleague Scott in a great tip of the week that you can see here.
After running the query, you can check the results in Studio itself, or directly in the SQL Server database if you have access:

Have a great week!
------------------------------
Daniel del Val
SE
*Precisely Software Inc.
------------------------------