Reference

Table [Dynamic Table]

Description

No description available

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.
Method Supported Reference EndPoint
SELECT get_[dynamic_endpoint_name]
INSERT
UPDATE
UPSERT
DELETE
LOOKUP

Examples

SSIS

Use OData Connector in API Source component to read data or in API Destination component to read/write data:

Read from [Dynamic Table] table using API Source

API Source - OData
Read and write OData API data effortlessly. Query, integrate, and manage entities and feeds — almost no coding required.
OData
[Dynamic Table]
There are no parameters to configure.
SSIS API Source - Read from table or endpoint

Read/write to [Dynamic Table] table using API Destination

API Destination - OData
Read and write OData API data effortlessly. Query, integrate, and manage entities and feeds — almost no coding required.
OData
[Dynamic Table]
Select
There are no parameters to configure.
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read all records from a JSON OData service table

<p>Gets all rows from the <code>Customers</code> entity set when the service is configured for JSON. Use the table name that matches your OData metadata (entity set name).</p> 

SELECT * FROM Customers

Filter records using server-side criteria

<p>Gets only customers that match the given OData filter expression. The filter is applied on the server. Use the service's filter syntax (e.g. <code>Country -eq 'USA'</code>).</p> 

SELECT * FROM Customers WITH(SearchCriteria='Country eq ''USA''')

Filter records using client-side criteria

<p>Gets only customers where <code>Country</code> equals <code>USA</code>. Filtering is done after data is retrieved (client-side). Use for simple equality filters when server-side syntax is not required.</p> 

SELECT * FROM Customers WHERE Country = 'USA'

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Read all records from a JSON OData service table

<p>Gets all rows from the <code>Customers</code> entity set when the service is configured for JSON. Use the table name that matches your OData metadata (entity set name).</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Customers';

EXEC (@MyQuery) AT [LS_TO_ODATA_IN_GATEWAY];

Filter records using server-side criteria

<p>Gets only customers that match the given OData filter expression. The filter is applied on the server. Use the service's filter syntax (e.g. <code>Country -eq 'USA'</code>).</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Customers WITH(SearchCriteria=''Country eq ''''USA'''''')';

EXEC (@MyQuery) AT [LS_TO_ODATA_IN_GATEWAY];

Filter records using client-side criteria

<p>Gets only customers where <code>Country</code> equals <code>USA</code>. Filtering is done after data is retrieved (client-side). Use for simple equality filters when server-side syntax is not required.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Customers WHERE Country = ''USA''';

EXEC (@MyQuery) AT [LS_TO_ODATA_IN_GATEWAY];