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_table_rows
INSERT insert_push_dataset_table_rows
UPDATE
UPSERT
DELETE
LOOKUP

Examples

SSIS

Use Power BI 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 - Power BI
Read and write Microsoft Power BI data effortlessly. Access, refresh, and manage datasets, reports, and dashboards — almost no coding required.
Power BI
[Dynamic Table]
Required Parameters
TableName Fill-in the parameter...
Optional Parameters
DatasetId
WorkspaceId
DaxFilter true
EnableCustomReplace 1
SearchFor (?<=(\s*)({|,))(?<TableWithColumn>"[$TableName$]\[(?<Column>.+?)\]")(?=:)--regex
ReplaceWith $4
SSIS API Source - Read from table or endpoint

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

API Destination - Power BI
Read and write Microsoft Power BI data effortlessly. Access, refresh, and manage datasets, reports, and dashboards — almost no coding required.
Power BI
[Dynamic Table]
Select
Required Parameters
TableName Fill-in the parameter...
Optional Parameters
DatasetId
WorkspaceId
DaxFilter true
EnableCustomReplace 1
SearchFor (?<=(\s*)({|,))(?<TableWithColumn>"[$TableName$]\[(?<Column>.+?)\]")(?=:)--regex
ReplaceWith $4
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read table rows

<p>Gets rows from a table in the default dataset. Supply the table name in the <code>FROM</code> clause.</p> 

SELECT * 
FROM MyTable

Bulk create rows using SQL Server data

<p>Bulk inserts rows into a push dataset table using data from SQL Server. Supply <code>DatasetId</code> and <code>WorkspaceId</code> in the connection string.</p> 

INSERT INTO Products 
SOURCE(
  'MSSQL',
  'Data Source=localhost\developer;Initial Catalog=Northwind;Integrated Security=true',
  '  SELECT T.* FROM (       SELECT  TOP 50                
      ProductName AS [Name]              
      ,C.CategoryName AS Category              
      ,Discontinued AS IsComplete              
      ,GETDATE() AS ManufacturedOn              
      ,CAST(UnitPrice * ReOrderLevel * 100 AS BIGINT) AS Sales              
      ,CAST(UnitPrice AS DECIMAL) AS Price       
      FROM Northwind.dbo.Products AS P       
      JOIN Northwind.dbo.Categories C ON P.CategoryId = P.CategoryId       
      ) AS T              
      CROSS JOIN GENERATE_SERIES(1, 2000)             
      -- COMMENT:  50 x 2000 = 100 000 rows  
   '
) 
CONNECTION(
  Parameters = '[{ Name: "TokenUrl",Value:"https://login.microsoftonline.com/organizations/oauth2/v2.0/token"}                         
  ,{ Name: "DatasetId",Value: "6a0e04da-a6e4-4533-abe4-30fcabd0e2a5"},                      
  { Name: "WorkspaceId",Value: "848353e2-f3b1-4fb4-89d7-44e84b8bdf9f"}]'
)

Create rows

<p>Inserts rows into a push dataset table using a standard <code>INSERT</code> statement.</p> 

INSERT INTO MyTable(MyColumn1, MyColumn2, MyColumn3, MyColumn4, MyColumn5)
VALUES (1001, 'Glass', true, '2001-02-03', 195.95)

Create rows in a dataset

<p>Inserts rows into a push dataset table in a specific dataset. Supply <code>DatasetId</code> in the <code>WITH</code> clause.</p> 

INSERT INTO MyTable(MyColumn1, MyColumn2, MyColumn3, MyColumn4, MyColumn5)
VALUES (1001, 'Glass', true, '2001-02-03', 195.95)
WITH (DatasetId='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee')

SQL Server

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

Read table rows

<p>Gets rows from a table in the default dataset. Supply the table name in the <code>FROM</code> clause.</p> 

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

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];

Bulk create rows using SQL Server data

<p>Bulk inserts rows into a push dataset table using data from SQL Server. Supply <code>DatasetId</code> and <code>WorkspaceId</code> in the connection string.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Products 
SOURCE(
  ''MSSQL'',
  ''Data Source=localhost\developer;Initial Catalog=Northwind;Integrated Security=true'',
  ''  SELECT T.* FROM (       SELECT  TOP 50                
      ProductName AS [Name]              
      ,C.CategoryName AS Category              
      ,Discontinued AS IsComplete              
      ,GETDATE() AS ManufacturedOn              
      ,CAST(UnitPrice * ReOrderLevel * 100 AS BIGINT) AS Sales              
      ,CAST(UnitPrice AS DECIMAL) AS Price       
      FROM Northwind.dbo.Products AS P       
      JOIN Northwind.dbo.Categories C ON P.CategoryId = P.CategoryId       
      ) AS T              
      CROSS JOIN GENERATE_SERIES(1, 2000)             
      -- COMMENT:  50 x 2000 = 100 000 rows  
   ''
) 
CONNECTION(
  Parameters = ''[{ Name: "TokenUrl",Value:"https://login.microsoftonline.com/organizations/oauth2/v2.0/token"}                         
  ,{ Name: "DatasetId",Value: "6a0e04da-a6e4-4533-abe4-30fcabd0e2a5"},                      
  { Name: "WorkspaceId",Value: "848353e2-f3b1-4fb4-89d7-44e84b8bdf9f"}]''
)';

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];

Create rows

<p>Inserts rows into a push dataset table using a standard <code>INSERT</code> statement.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO MyTable(MyColumn1, MyColumn2, MyColumn3, MyColumn4, MyColumn5)
VALUES (1001, ''Glass'', true, ''2001-02-03'', 195.95)';

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];

Create rows in a dataset

<p>Inserts rows into a push dataset table in a specific dataset. Supply <code>DatasetId</code> in the <code>WITH</code> clause.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO MyTable(MyColumn1, MyColumn2, MyColumn3, MyColumn4, MyColumn5)
VALUES (1001, ''Glass'', true, ''2001-02-03'', 195.95)
WITH (DatasetId=''aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'')';

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];