Copy data to Excel tables or sheets
The main functionality in the SQLinExcel workbook is the ability to easily and efficiently transfer data from an SQL server to Excel. The data selection is specified with the SQL in Excel, but the data crunching is done on the SQL server and easily inserted/updated in multiple Excel tables and pivot tables.
In addition to copying/updating Excel tables og pivots as indicated above, it is also provides the option to append data to an Excel table and show table content in a dialog box.
Another commonly used functionality is the option to search and replace strings in the SQL before it is sent to the server. This provides flexibility and serves to highlight relevant user input.
Introduction to #_T_ (tables) and #_P_ (pivot tables)
As we saw previously, the "#_T_" prefix can be used to insert/update one or more an Excel tables, and "#_P_" prefix can be used to insert/update one or more pivot tables:
Running the SQL again will update the target content of the table. If the number of rows in the table changes, it will automatically delete the rows with old data.
By default, the table data is from cell A2 and onwards, and headers are written in the row above. This implies that "into #_T_DemoTable" will yield the same result as "#_T_DemoTable_A2_H".
Overview of temporary table names (#_X_xxx)
Temporary table prefix
Options / further information
|#_T_||Create table in Excel||
Include _sheetname_cell (#_T_Demo_B3)
Further add _H to include headers (#_T_Demo_B3_H)
Further add _A to append data below the existing (#_T_Demo_B3_A)
|#_P_||Create pivot table in Excel||
Include _sheetname (#_P_Demo)
|#_M_||Show message box||
Optionally include _X, where X is the number of rows (#_M_Message_5)
By default a maximum of 10 rows are shown.
|#_R_||Copy data from Excel to server||
Always add _sheetname_cell referencing the first data cell.
Read more in the use Excel data in query user guide section
|#_F_||Export data to a text file||
Specify text file properties under settings.
Read more in the create text files user guide section
Note: In all cases, duplicate column names are not allowed.
Example - table (#_T_) pivot (#_P_) and search and replace
This is an example where a table and a pivot table is created/updated in the same workflow (when "Run SQL" is pressed). The example also shows how the search and replace function can highlight relevant user input. In this case the number of rows to include in the table is set to 200 and can easily be modfied by the user without reviewing the SQL. The example also creates/updates a pivot table holding all the 100,000 random records.
Click here to get the sample code.
Example - append data to table (#_T_xxx_xx_A) and show messagebox (#_M_)
This is an example where data in a table is appended instead of replaced when the SQL is run. In addition by inserting into a temporary table with the prefix "#_M_", a messagebox with the content is displayed. This provides a simple option for communicating a status to the user.
A messagebox initiated by the "#_M_" prefix will be displayed every time the SQL runs, unless the temporary table contains 0 rows.
Running the SQL with (#_T_RandomTable_A2_A) two times with 10 rows to add each time, will result in a total of 20 rows:
If the SQL cannot run, most likely something is wrong with the syntax, the selected database or a column without a name is being inserted into a (temporary) table.
To located the issue press the "SQL to clipboard" and paste the clipboard content into Server Management Studio and run the SQL. If the SQL fails to run in Server Management Studio, it will not run from Excel either. Check any error message from Server Management Studio and make the necessary corrections in the SQL in Excel.
A common issue is the attempt to insert an unnamed column into a temporary table. In addition SQLinExcel does not allow duplicate names in columns, requiring the user to give all columns unique names. Excel is in general not case sensitive, and hence the two column names "Text" and "text" will not qualify as unique, when used together.
Also ensure that the relevant database is used. In Server Management Studio the database can typically be selected in the upper left corner. If a specific database is chosen in Server Mangement Studio (lets say database "ABC") then you need to include a "USE ABC" statement in the beginning of your SQL in Excel.