ODBC Driver for Microsoft Excel Online

Build 24.0.9060

Executing Queries

This section describes how to execute queries using MySQL remoting from any tool that can connect to MySQL.

When executing queries, use a two-part name, in the format:

[<Schema Name>].[<Table Name>] 
where
  • Schema Name is the name of the system schema or user-defined schema. (This is normally the service name.)
  • Table Name is the name of the table you are copying data from.

SELECT Statements

To create new records:
SELECT * FROM [ExcelOnline].[Test_xlsx_Sheet1] WHERE Column2 = 'Bob' 
Note: MySQL treats identifiers as case-insensitive, even if the data source supports case-sensitive identifiers. This can be a problem when the database contains multiple tables of the same name in a single schema, because when using MySQL it is not possible to query tables of the same name that only vary by upper or lower case-sensitive (for example, a schema with one table named Account, and a second table named ACCOUNT).

INSERT, UPDATE, and DELETE Statements

To insert, update, or delete a single row of data against a writeable database, use the two-part name. For example:
INSERT INTO [ExcelOnline].[Test_xlsx_Sheet1] (Column2) VALUES ('Bob'); 

Bulk Operations

To perform bulk operations, use syntax similar to the following:

You can run a bulk INSERT using a SELECT query in place of a VALUES list.

INSERT INTO [ExcelOnline].[Test_xlsx_Sheet1] ([Column2]) 
SELECT [Column2] FROM [Local_Test_xlsx_Sheet1] 
You can run a bulk UPATE using a JOIN with a local table.
UPDATE [ExcelOnline].[Test_xlsx_Sheet1]  
INNER JOIN [Local_Test_xlsx_Sheet1] ON [Local_Test_xlsx_Sheet1].[Id] = [ExcelOnline].[Test_xlsx_Sheet1].[Id] 
SET [ExcelOnline].[Test_xlsx_Sheet1].[Column2].[Local_Column2] 
You can run a bulk DELETE using the IN filter with sub-SELECT query.
DELETE FROM [ExcelOnline].[Test_xlsx_Sheet1 WHERE [Id] IN (SELECT [Id] FROM Local_Test_xlsx_Sheet1) 

Execute Stored Procedures

To execute the stored procedure, use:
Call CreateJob('Insert')

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060