Endpoint Insert Rows into Push Dataset Table

Name

insert_push_dataset_table_rows

Description

Inserts rows into a Push Dataset Table

Related Tables

[Dynamic Table]

Parameters

Output Columns

Input Columns

Examples

SSIS

Use Power BI Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Destination

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to insert rows into push dataset table:

API Destination - Power BI

Connect to your Power BI account and retrieve data, refresh datasets, etc.

Power BI

[Dynamic Table]

Insert

Required Parameters
TableName	Fill-in the parameter...
Optional Parameters
DatasetId
WorkspaceId
ContineOn404Error	True
EnableRawOutputModeSingleRow	True
RawOutputDataRowTemplate	{}

ODBC application

Use these SQL queries in your ODBC application data source:

Tables - Insert / Update / Delete Rows for a specified Workspace and Dataset

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"}]'
)

Tables - Using an INSERT statement

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

Tables - Using an INSERT statement in a specified Dataset

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:

Tables - Insert / Update / Delete Rows for a specified Workspace and Dataset

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];

Tables - Using an INSERT statement

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];

Tables - Using an INSERT statement in a specified Dataset

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];