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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (3,980)