Hi iam trying to add a dynamic data table into sql table using INSERT INTO Table SELECT * FROM %DATATABLE%. But iam not able to add datatable to sql table. I have used%% because iam placing q datatable variable in the query. Can anyone please help me. I want to achieve this on power automate desktop only
Hello @Anonymous ,
Please ensure that the value that is stored in the variable %DATATABLE% is correct. Is the output stored with correct table name?
Also, ensure the SQL query that you are using to insert datatable into SQL table is appropriate.
For your reference: https://www.w3schools.com/sql/sql_insert.asp
Thanks and hope it can help you.
Did I answer your question? Please consider to Mark my post as a solution! to guide others
I have exactly the same need, do you succeed in a way or in other?
It's even more simpler, I just want to do :
DROP TABLE IF EXISTs Table
I tried the syntax of @Anonymous 's original post if I can execute the INSERT statement, but without success. I myself doubt that it is really possible to do so. Someone please correct me if I am wrong.
As a workaround, I came up with making the Excel book referable from the database engine as a "Linked Table".
For an example, in Microsoft Access you can select an Excel book and choose option [Link to the data source by creating a linked table].
You can read data from Excel worksheet and treat it as a table inside Access.
After that in PAD, I can execute SQL query in below syntax to SELECT all records from Excel book as a Linked Table "EmployeesLinkedTable" and INSERT them into Access table "Employees".
INSERT INTO Employees
SELECT * FROM EmployeesLinkedExcel;
I believe you can do the same thing with other database engine products such as SQL Server. Hope this information is useful to you.
I have a similar use case to import (or insert) a data table from Excel to a SQL database.
I tried different ways to load the data table (via SQL connect and via direct reading from xls) and to connect to the SQL database but failed as well to use a SQL query to pass the complete data table to the target database.
After giving it another thought, my current understanding is that the SQL query is performed inside the SQL connection.
That means, as long as the data table from the source does not exist in the database, the SQL query referring to it won’t work.
The example with MS Access works as the source table is present in the database already.
One solution is to import via a csv file.
Alternatively, it should work (rather slowly), if you insert the data to the target table row-by-row as values.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!