SQL Query Examples
 PreviousNext

Content

Driver SQL Syntax

On this page you will find various query examples for Excel ODBC Driver / Data Gateway Connector. This driver offers highly flexible Client Size SQL Query engine which runs on client side to offer rich query language like a traditional RDBMS. Keep in mind that its not same as traditional database SQL so many features might not be supported also this engine is invoked on client side so there might be some performance issue if you have very large dataset. In below section you can see high level SQL Grammer / Syntax for writing SQL query for this driver.

SELECT Syntax / INTO

Below section shows use of SELECT statement. It can output data or store data into temporary table (must be named with prefix # (i.e. #temp_table) ). 

SELECT 
 [DISTINCT] [* | [ expression [[AS] column_name_alias] [, ...] ]
 [INTO #temp_table_name]
 [FROM table_name]
 [WHERE condition [, ...] ]
 [GROUP BY expression [, ...] ]
 [HAVING condition [, ...] ]	
 [ORDER BY expression [ASC | DESC] [, ...] ]
 [LIMIT row_count] 
 [WITH (option_name=option_value] [, ...]) ] [;]
 

option_name:=IDENTIFIER
option_value:= NUMBER | STRING

Select using Temp Table

This driver supports storing data into temporary table and then using it later in the same script. You can issue multiple SQL statements in the same script seperated by semicolon (;). For output only last Non-SELECT INTO statement is used. When you query #temp_table you get support for INNER / OUTER JOIN statements. 

 SELECT [DISTINCT] [* | [ expression [[AS] column_name_alias] [, ...] ] 
 FROM #temp_table_name
 [[INNER | OUTER | CROSS] JOIN #temp_table_name [alias] ON condition 
   [join_statement] 
   [join_statement] 
   ... 
 ]
 [WHERE condition [, ...] ]
 [GROUP BY expression [, ...] ]
 [HAVING condition [, ...] ]	
 [ORDER BY expression [ASC | DESC] [, ...] ]
 [LIMIT row_count]

Multiple SQL Statments

This driver supports executing multiple statements in a single command. You can issue multiple SQL statements in the same script seperated by semicolon (;). For output only last Non-SELECT INTO statement is used. When you query #temp_table you get support for INNER / OUTER JOIN statements. 

select_into_temp; 
[select_into_temp;] 
[select_into_temp;]
...
select_from_temp_table;

Comment Syntax

ZappySys Driver supports single line comment and block comments anywhere in SQL 

--some comment in single line 
|
/* some block comment */

UNION / UNION ALL Syntax

You can use UNION or UNION ALL key word to output multiple temp tables with similar structure in a single resultset. UNION ALL keyword doesnt apply DISTINCT operation to eliminate duplicate rows so its faster. 

select_from_temp_table_1
   UNION | UNION ALL
select_from_temp_table_2
  [UNION | UNION ALL]
select_from_temp_table_3	
...
...

Stored Procedure Syntax

This driver supports writing Stored Procedure. This is useful when writing reusable parameterized SQL for ease of use. Rather than calling long SQL from application you can call short name with parameters to invoke same logic. Stored procedure support multi-statements using TEMP tables (i.e. SELECT ... INTO #mytemptable clause). To invoke Stored Proc use EXEC keyword before its name. See below Syntax.
CREATE PROCEDURE 

CREATE PROCEDURE proc_name 
  [@parameter_name [= 'Some Default Value']  ] 
  [@parameter_name [= 'Some Default Value']  ] 
  ...
  ...
AS 
  sql-statement; 
 [sql-statement | sql-temp-table-usage] ;
 [sql-statement | sql-temp-table-usage] ;
 ...
 ...
Executing PROCEDURE 
EXEC proc_name [value1] [,value2] ... [,valueN]
Deleting PROCEDURE 
DROP PROCEDURE proc_name

SQL Examples

SELECT

This example shows how to read data starting from a specific cell. 
SELECT * FROM [Sheet1] -- WITH(RangeStartCell='A1')

SELECT with StartRange

This example shows how to read data from specific cell range (when RangeEndCell is not supplied it auto detects last cell). 
SELECT * FROM [Sheet1] WITH(RangeStartCell='A1')

SELECT with StartRange and EndRange

This example shows how to read data from specific cell range. 
SELECT * FROM [Sheet1] WITH(RangeStartCell='A1', RangeEndCell='F100')

SELECT from data table without columns

This example shows how to read data where no columns defined. 
SELECT * FROM [Sheet1] WITH(RangeStartCell='A1', Headerless=1)

SELECT with skip rows from top

This example shows how to read data after skipping first 5 rows (right after columns row). 
SELECT * FROM [Sheet1] WITH(RangeStartCell='A1', SkipRows=5)

INSERT

This example shows how to call INSERT row action for a specified table. Optionally you can supply Parameters via WITH clause. 
INSERT INTO [Sheet1](Col1,Col2) Values('something', 1234)

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(RangeStartCell='A2', TreatValueAsFormula=0, Headerless=0, PreserveTargetFormat=1, ClearTargetSheetBeforeWrite=0, RangeToClearBeforeWrite='A1:F100', DateFormat='yyyy-MM-dd' , DateTimeFormat='yyyy-MM-dd HH:mm:ss')

--CONNECTION( Path='C:\temp\data.xlsx'
--        , ExcelVersion='v2013' --Default, v2007, v2010, v2013
--        , OpenPassword='something'
--        , EditPassword='something'
--        , OutputBadDateAsNull=1
--        , CreateFileIfMissing=1
--        , RetainSameConnection=1)

INSERT With Options

This example shows how to call INSERT row action for a specified table. Optionally you can supply Parameters via WITH clause. 
INSERT INTO [SheetName] (Col1,Col2) Values('something', 1234) WITH(  RangeStartCell='A1' --Use this option to start Insert at a specific location
--, Headerless=0 --Use this option to indicate no columns found (skip writing header line)
--, TreatValueAsFormula=0 --use this option if some value has formula which starts with = sign e.g. =C1+C2
--, ClearTargetSheetBeforeWrite=0 --Clears entire sheet before write
--, RangeToClearBeforeWrite='A2:F10000' --Clears specific range of cells before write
--, PreserveTargetFormat=1 --use this option along with ClearTargetSheetBeforeWrite or RangeToClearBeforeWrite
) 

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(RangeStartCell='A2', TreatValueAsFormula=0, Headerless=0, PreserveTargetFormat=1, ClearTargetSheetBeforeWrite=0, RangeToClearBeforeWrite='A1:F100', DateFormat='yyyy-MM-dd' , DateTimeFormat='yyyy-MM-dd HH:mm:ss')

--CONNECTION( Path='C:\temp\data.xlsx'
--        , ExcelVersion='v2013' --Default, v2007, v2010, v2013
--        , OpenPassword='something'
--        , EditPassword='something'
--        , OutputBadDateAsNull=1
--        , CreateFileIfMissing=1
--        , RetainSameConnection=1)

UPDATE

This example shows how to call UPDATE on a table. Col1, Col2... are dummy name and Col1 starts from RangeStartCell (Default is A1). 
UPDATE [SheetName] SET Col1='something', Col2=1234 WITH(RangeStartCell='A5') 

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(RangeStartCell='A2', TreatValueAsFormula=0, Headerless=0, PreserveTargetFormat=1, ClearTargetSheetBeforeWrite=0, RangeToClearBeforeWrite='A1:F100', DateFormat='yyyy-MM-dd' , DateTimeFormat='yyyy-MM-dd HH:mm:ss')

--CONNECTION( Path='C:\temp\data.xlsx'
--        , ExcelVersion='v2013' --Default, v2007, v2010, v2013
--        , OpenPassword='something'
--        , EditPassword='something'
--        , OutputBadDateAsNull=1
--        , CreateFileIfMissing=1
--        , RetainSameConnection=1)

Bulk INSERT (Read from MSSQL, ODBC) - SOURCE Clause

This example shows how to use SOURCE clause to perform Bulk INSERT operation. In this example we are reading many rows from external system (e.g. MSSQL) and supplying to INSERT command. Make sure you external SQL outputs exact same column names expected by Target table used in Insert. Notice how we have aliased columns in External SQL to match with InputColumn (i.e. TargetCol1) and Parameter (i.e. $$MyParam2). Whenever possible supply parameters via WITH clause (i.e. same values for all rows) but if you have use case to supply different parameter value for each row then alias source column with $$ prefix (i.e. $$MyParam2 ). See help file to know exact name for parameter you like to map.
Syntax : SOURCE('MSSQL|OLEDB|ODBC', 'driver-connection-string', 'source-sql')
--OR-- SOURCE('source-sql-same-connection') 
INSERT INTO MyTable
--Column list / Values omitted when you use SOURCE clause
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=true','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')

--For bulk input, map columns in External Query (Must use alias column name to match INSERT command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=C:\AccountsToInsert.csv','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM $')
--SOURCE('ODBC', 'DSN=MyDSN','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
--SOURCE('OLEDB', 'Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')

WITH(RangeStartCell='A1', TreatValueAsFormula=0, ClearTargetSheetBeforeWrite=0, PreserveTargetFormat=0, RangeToClearBeforeWrite='A2:F10000', DateFormat='dd/MM/yyyy', DateTimeFormat='dd/MM/yyyy HH:mm:ss' )

Bulk UPDATE (Read from MSSQL, ODBC) - SOURCE Clause

This example shows how to use SOURCE clause to perform Bulk UPDATE operation. In this example we are reading many rows from external system (e.g. MSSQL) and supplying to UPDATE command. Make sure you external SQL outputs exact same column names expected by Target table used in Update. Notice how we have aliased columns in External SQL to match with InputColumn (i.e. TargetCol1) and Parameter (i.e. $$MyParam2). Whenever possible supply parameters via WITH clause (i.e. same values for all rows) but if you have use case to supply different parameter value for each row then alias source column with $$ prefix (i.e. $$MyParam2 ). See help file to know exact name for parameter you like to map.
Syntax : SOURCE('MSSQL|OLEDB|ODBC', 'driver-connection-string', 'source-sql')
--OR-- SOURCE('source-sql-same-connection') 
UPDATE [SheetName]
--SET clause is omitted when you use SOURCE clause
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=true','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')

--For bulk input, map columns in External Query (Must use alias column name to match UPDATE command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=C:\AccountsToUpdate.csv','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM $')
--SOURCE('ODBC', 'DSN=MyDSN','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
--SOURCE('OLEDB', 'Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')

WITH(RangeStartCell='A1', TreatValueAsFormula=0, ClearTargetSheetBeforeWrite=0, PreserveTargetFormat=0, RangeToClearBeforeWrite='A2:F10000', DateFormat='dd/MM/yyyy', DateTimeFormat='dd/MM/yyyy HH:mm:ss' )

Bulk UPDATE (Read from Same Excel file) - SOURCE Clause

This example shows how to update Sheet1 by reading data from Sheet2 (Where source and target on the same workbook file) 
UPDATE [Sheet1]
--SET clause is omitted when you use SOURCE clause
SOURCE('select Name,Age from [Sheet2] WITH(RangeStartCell=''A1'') ')

Custom Metadata / Override Mode

SELECT id,name,checknumber FROM MyTable
WITH
(
--//Custom Metadata (Direct Mode)<<<
--//Add all possible columns here and it will be part of metadata (this approach ignores any metadata comes from table itself). This approach usually helpful when Table has dynamic data (scan 300 rows to detect types) which may cause issue if zero records found.
META = 'id:int;checknumber:string(10);name:string(10);some_extra_column:datetime'

--//Custom Metadata with Override  (Direct Mode)<<<
--You can use @OverrideMode:1 in the column list to override meta for specific column(s) only and use all other columns from reported metadata for table - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause
--META = '@OverrideMode:1;id:int;checknumber:string(10)'

--//Metadata from File (supply metadata stored in file)
--META = 'c:\mytable_meta.txt'
)

Using Connection Clause

This examples shows how to supply connection information via CONNECTION clause in the sql query. CONNECTION clause takes precedence over the default values configured for data source using UI 
SELECT * FROM Customers
--WITH(Param1 = 'something', Param2 = 'something')
CONNECTION(
    , Path = 'c:\test.xlsx'
--  , ExcelVersion = 'v2013' --Default, v2007, v2010, v2013
--  , OpenPassword = 'something'
--  , EditPassword = 'something'
--  , OutputBadDateAsNull  = 1
--  , CreateFileIfMissing = 1
--  , RetainSameConnection  = 1
)

DateTime field Compare in WHERE / ORDER BY

This example shows how to use date time field in WHERE or / ORDER BY. Date Time field needs special handling. You have to use DATETIME function else it will do string compare rather true date time compare. Foe example WHERE OrderDate<=DATETIME('1997-12-31 00:00:00') -- OR WHERE OrderDate<=DATE('1997-12-31'). You must use DATETIME either left side or right side. Also must use 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd HH:mm:ss.fff' format (where fff is milliseconds) 
SELECT * FROM [Sheet1] 
WHERE OrderDate<=DATETIME('1997-12-31 00:00:00') -- OR use DATE('1997-12-31') DateTime column can be queried this way. You must wrap DATETIME function around it. Also must use 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss'  or 'yyyy-MM-dd HH:mm:ss.fff' format (where fff is milliseconds)
ORDER BY DATETIME(OrderDate)
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Path='c:\test.xlsx')

WITH / CONNECTION Clause

This example shows how to use WITH clause along with Connection clause. Use WITH clause to supply request parameters (Click on [Query Builder] to build request correctly). You can use CONNECTION clause to override any connection level parameters. 
SELECT * FROM [Customers]
LIMIT 3
WITH(RangeStartCell='A1')
CONNECTION(Path='c:\test.xlsx')

Filter on Date, Group By, Order By, Limit Clause

This example shows how to use WHERE clause and search by datetime, use GROUP BY / HAVING, ORDER BY and LIMIT clauses. You can use OData Connector with Northwind Service URL to run this example. 
SELECT 
  Country AS Invoice_Country
, SUM(UnitPrice * Quantity) AS Invoice_Total
FROM [Sheet1] 
WHERE Discount > 0
AND OrderDate<=DATETIME('1997-12-31 00:00:00') 
--or use OrderDate<=DATE('1997-12-31') 
--Valid formats are yyyy-MM-dd, yyyy-MM-dd HH:mm:ss or yyyy-MM-dd HH:mm:ss.fff (where fff is milliseconds)
GROUP BY Country 
HAVING SUM(UnitPrice * Quantity) > 1000
ORDER BY 
  Invoice_Total DESC
--,DATETIME(OrderDate) 
LIMIT 3
CONNECTION(Path='c:\test.xlsx');

Case Statement

This example shows how to write CASE statement to select / output different value based on multiple conditions. 
SELECT 
  CustomerID
, CompanyName 
, Country
, Region
--Pattern#1 - Match by clause
, CASE 
   WHEN Country IN('UK','Germany','France') OR Region='BC' THEN 'EU' 
   WHEN Country IN('USA','Maxico','Canada') THEN 'NA' 
   WHEN Country IN('Brazil','Argentina','Venezuela') THEN 'SA'
   ELSE 'Unknown'
 END Pattern1

--Pattern#2 - Match by static value or simple expression
, CASE Country 
   WHEN 'U'+'K' THEN 'EU' 
   WHEN 'USA' THEN 'EU' 
   WHEN 'Brazil' THEN 'SA'
   WHEN 'Argentina' THEN 'SA'
   WHEN 'Venezuela' THEN 'SA' 
   ELSE 'Unknown'
 END Pattern2 

FROM Customers
CONNECTION(Path='c:\test.xlsx')

SELECT INTO / Multiple Statements

This example shows how to use SELECT INTO statement and use of multiple statements in the same batch. Output from only last SELECT is returned if you have multiple SELECT * FROM in the same batch. Each statement must be separated by semi-colon ( ; ). Also notice use or DateTime column. To compare date in WHERE clause or use Order BY clause you must use DATETIME function else it will be treated as string value (e.g. use WHERE o.OrderDate<=DATETIME('1997-12-31 23:12:59'). Also must use 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd HH:mm:ss.fff' format (where fff is milliseconds) 
SELECT * INTO #tmpCustomers FROM [Sheet1]
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Path='c:\test.xlsx')

SELECT * INTO #tmpOrders FROM [Sheet2]
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Path='c:\test.xlsx')

--JOIN 2 temporary tables
select c.CompanyName,Count(*) TotalOrders 
FROM #tmpCustomers c 
INNER JOIN #tmpOrders o ON o.CustomerID=c.CustomerID
WHERE o.OrderDate<=DATETIME('1997-12-31 23:12:59') --date format must be yyyy-MM-dd or yyyy-MM-dd HH:mm:ss[.fff]
GROUP BY  c.CompanyName;

UNION ALL / UNION Statement

This example shows how to write UNION or UNION ALL statement to combine results from multiple SQL queries. To remove duplicate rows from result use UNION rather than UNION ALL. 
SELECT * into #tmpCustomersUK FROM [SheetUK] Where Country='UK'
--WITH (MyParam1='something1', MyParam2='something2');

SELECT * into #tmpCustomersUSA FROM [SheetUSA] WHERE Country='USA'
--WITH (MyParam1='something1', MyParam2='something2');

select * from #tmpCustomersUK
UNION ALL
select * from #tmpCustomersUSA;

Basic SQL queries

Basic query examples. 

--Whenever Possible try to write Simple Query. When you use WHERE / GROUP BY / ORDER BY Expressions. Driver may invoke Client side processing if server side doesnt support that construct.
--For comment use line comment with two dash (e.g. --my comment) or use block comment for multi line comment (e.g. /*my comment*/ )
--Any query must contain at least two required parts
--data source (or table) names and a list of column names
--column names come first (preceded by SELECT keyword)
--table names follow (preceded by FROM keyword)

--Here is the query that returns all rows with all columns (* stands for all columns) from 'Accounts' table
SELECT * FROM Accounts

--Use quoted identifiers when you have space or other character in names (e.g. Column or Table name contains space)
SELECT ID, "Account Name" FROM "Closed Accounts"
SELECT ID, [Account Name] FROM [Closed Accounts]

--returns all rows from 'Accounts' table, each row will have Id and Amount columns
SELECT Id, Amount FROM Accounts

--returns only those rows of 'Accounts' table that meet next condition: Amount is greater or equal to 1000.0
SELECT Id, Amount FROM Accounts WHERE Amount >= 1000.0

--returns only those rows of 'Accounts' table that meet next condition: Amount is greater than 1000 or DepartamentID is equal to 123 
SELECT Id, Amount FROM Accounts WHERE (Amount > 1000.0 OR DepartamentID = 123)

--returns only those rows of 'Accounts' table that meet next condition: DepartamentID is equal to 1, 101 or 202 
SELECT Id, Amount FROM Accounts WHERE DepartamentID IN (1, 101, 202)

--returns only those rows of 'Accounts' table that meet next condition: DepartamentID is equal to 321 and Amount is less than 100 
SELECT Id, Amount FROM Accounts WHERE DepartamentID = 321 AND Amount < 100

--returns only those rows of 'Persons' table that meet next condition: Name column value starts with 'John'
SELECT * FROM Person WHERE Name LIKE 'John%'

--case statement
SELECT 
 FirstName,
 LastName,
 CASE Country 
  WHEN 'US' THEN 'NA' 
  WHEN 'Canada' THEN 'NA'
  WHEN 'India' THEN 'Asia'
  WHEN 'China' THEN 'Asia'
  ELSE 'Unknown' 
 END ColRegion 
 FROM Person

Copyrights reserved. ZappySys LLC.