Answered

Add new column to Datatable via sql statement

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello all,

I currently need help with how to add a new column to my datatable. I have read my data through a SQL statement and I want to add a new column with a query "ALTER TABLE %QueryResult% ADD UserID VARCHAR(10)" but I kept getting the error: Error in SQL statement Syntax error in ALTER TABLE statement.

Is this possible to achieve?

Any help will be appreciated!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (7)

Answers (1)

Sort by

VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

'QueryResult' is a Datatable variable of PAD and the SQL command won't work on that variable.

Instead add a column to your Excel
And then execute Line nos 10 and 11 from your screenshot.

Here is the logic:
- If the Excel looks like this

- Find the first free column

- Then add a new column

- After executing the flow it will add a new column

- Now Run your line nos. 10 and 11
The resulting datatable 'QueryResult' should have the new column.

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Another option:
Line no 12.
Since Sheet2$ is now your database table, try adding column to that instead of adding to QueryResult.

ALTER TABLE [Sheet2$] ADD column_name datatype

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

Like
a
(0)

Report
Copy link

Link copied!

Hi VJR,
Many thanks for your reply. I have tried using ALTER TABLE [Sheet2$] ADD column_name datatype yesterday but that also did not work. The workaround that I employed earlier was to create a new empty datatable with all the columns that I needed using Set variable, and then read the old datatable into it through a For each loop. This does not look like an efficient solution to me especially when the table has a lot of rows. This was why I thought it would be easier to achieve with an SQL query but it is unfortunate to learn that this is not possible.
I hope datatable activities in PDA will be developed soon so that we can have all the possibilities to easily carry out some of these actions that are available in other RPA solutions.

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous

Did you take a look at the approach in the first post after your original question which does not involve looping?

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

Like
a
(1)

Report
Copy link

Link copied!

Hi VJR,
Yes, I considered this option but it is not a suitable solution for me because I need to add 3 columns to this table and I need to update these columns in another workflow. Following this approach would mean that I have to implement write to Excel 3 times and then read the final excel sheet before I can use it in another workflow.

Was this reply helpful? Yes No
yoko2020 495 on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

Use excel template which contain all of the column.
You can write directly variable %QueryResult% to that excel template and save excel as new file.

Was this reply helpful? Yes No
Verified answer

VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous
Okay 🙂, using 3 statements are far much better than looping a datatable.

If you dont need anything further you can close this thread.
And if you need to suggest any datatable related activities to MSFT then you can do it here.

Was this reply helpful? Yes No