Unanswered

Insert data table into sql table

(3) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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

Categories:

Power Automate Desktop

I have the same question (0)

All responses (9)

Answers (0)

Sort by

Shalu Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
johnaustin 20 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
JRBC 2 on at

Like
a
(0)

Report
Copy link

Link copied!

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%

Regards.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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

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.

Good luck.

Was this reply helpful? Yes No
vvt 2 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
RaulRamirez 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, JRBC.

I need to do the exact same thing.

Did you succeed?

Was this reply helpful? Yes No
memorx 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi vvt, what do you mean by "One solution is to import via a csv file."?
Thank you!

Was this reply helpful? Yes No
Ash_0000000 4 on at

Like
a
(0)

Report
Copy link

Link copied!

Did anyone succeed in it? None of the answers in the forum is helpful and I doubt if there is even a way to read from an excel file and insert to a SQL database table using Power Automate.

Was this reply helpful? Yes No
NM-24071613-0 27 on at

Like
a
(0)

Report
Copy link

Link copied!

If data file (csv) location is same as sql server database, bulk insert query works fine.

Was this reply helpful? Yes No