Datatable to sql server

[Oriel]

Greetings to all.

I have a process which extracts information from an excel file and stores it in a datatable.

The detail is that I need a way in which I can store everything that is in that datatable in a table that I have from sql server.

I tried using sql (query) statements, but they have not given me results:

INSERT INTO [Table_name]
SELECT * FROM %ExcelData%

INSERT INTO [Table_name]
SELECT (%ExcelData%)

At the moment the only solution I have had is to use a 'foreach' loop and iterate between each record and perform an `INSERT INTO (A,B,C,D) VALUES (...)`, but the problem is that they are too many records and takes a long time to process them.

I also tried using `Bulk Insert`, but the SQL server is somewhere else than where I run Power Automate and I have no way to link to it.

I hope you can help me with my case, I would appreciate it a lot.

[Agnius]

The syntax you tried using will not work. What I could suggest is simply trying to use Join text to join your table into a single string with appropriate delimiters, so you can pass it into your insert statement. Alternatively, you basically need to loop through it.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Oriel]

Could you give me an example of what you mean?

I tried using a 'foreach loop' to get each row from the datatable, but it takes forever (6000 records) and my idea is to be able to store the entire datatable in the SQL Server table.

As a detail, the header names of the datatable and the table in sql match.

[Agnius]

6000 records shouldn't take forever. It takes a while when there are hundreds of thousands of records. Have you tried running it after saving your flow and closing the designer? When you run it in designer, it runs slower on purpose - so you can debug it. But when you run it from the console, it's significantly faster. Looping through 6000 records and inserting them to a SQL database should take a couple of minutes, if the database is responsive.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Oriel]

The 6000 records are not a problem, but before we used Navicat to load the records, so it was faster to do the import, but when doing it with Power Automate and foreach, it took us about 10 minutes to process the records vs the 40 seconds that He took us with Navicat, which in terms of time is quite the difference.

The idea then is to get rid of using Navicat as a record importer and use Power Automate for those daily storage tasks, and honestly I can't find a way other than a "loop" to be able to dynamically store everything through a query.

[Agnius]

Arguably, looping through the records and appending them to a string to be used in a single insert statement would work faster than doing an insert for each record. You might be limited to 1000 records in a single INSERT statement in SQL server, though. 

But you are right, I don't think there is a better way to do it, to be honest. You could try to use Join text on the entire table and then use some further text manipulation (such as Replace text) to format it in a way that would turn it into a single blob of text that can be used in a SQL statement. But I have honestly not tried it myself, so I cannot give you the exact sequence of steps to take. But you can run it in debugger and see what the output of Join text looks like.

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

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.