Insert data table into sql table

[Anonymous]

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

[Shalu]

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]

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

[JRBC]

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.

[Anonymous]

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.

[vvt]

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.

[RaulRamirez]

Hi, JRBC. 

I need to do the exact same thing. 

Did you succeed?

[memorx]

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

Thank you!

[Ash_0000000]

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.

[Nit1]

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