cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Insert data table into sql table

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

5 REPLIES 5
Shalu
Microsoft
Microsoft

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. 

Shalu

---------------------------------

Did I answer your question? Please consider to Mark my post as a solution! to guide others

johnaustin
Frequent Visitor

also watch out for any special characters in the SQL Insert statement. Any single quotes ' in string fields will need to be escaped.  

JRBC
New Member

Hello, 

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
SELECT *
INTO Table
FROM %ExcelData%

 

JRBC_0-1634567696545.png

 

Regards.

Anonymous
Not applicable

Hello @JRBC 

 

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

 

shindomo_0-1634680843721.png

 

You can read data from Excel worksheet and treat it as a table inside Access.

 

shindomo_1-1634680999096.png

 

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;

 

shindomo_3-1634681852308.png

 

I believe you can do the same thing with other database engine products such as SQL Server. Hope this information is useful to you.

 

Good luck.

vvt
New Member

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (1,948)