cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate IV
Advocate IV

SQL: Insert Row (V2) Assumes incorrect table definition

This has to do with table definitioin:


Reproduce it like this:

 

1) Create a table in your database

 

CREATE TABLE dbo.TestTable
(
ColumnA Varchar(max),
ColumnB Varchar(max)
)

2) Create a flow that uses this database and maybe inserts some random data into it.

3) Change the table defintion. Using Alter or drop the table and create a new one with the same name that looks like this:
CREATE TABLE dbo.TestTable
(
ColumnA Varchar(max),
ColumnB Varchar(max),

ColumnC Varchar(max)
)


Then try going to the flow and inserting data into this third column. The bug is that the values inserted will become "null". The flow doesn't recognized when table definitions have been changed, and there is no way to check what columns the flow inserts to, which leads to null values. 

 

Even though the actual "form" for the action is updated to three fields, the actual insert will still use the old column definition.

 

I,.e :

"INSERT INTO TABLE (ColumnA, ColumnB)" 
Instead of 
INSERT INTO TABLE (ColumnA, ColumnB, ColumnC),

Doesn't help if you drop the table in the database, or create new flow.


This bug also exists in PowerApps, where you are trying to insert values into a collection. The collection keeps the old column values, it doesn't care if you recreate the collection, so you constantly have to create new tables with entirely new names every time you add a column to your table.

 

Workaround:
Create a new table with your new table definitions with a new name.

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @agneum,

 

Thank you for your feedback on the SQL insert table feature and workaround. This is an issue of the SQL connector, which will be concerned in subsequent development.

It will be useful if anyone is searching for a solution to solve this problem.

You could go ahead and mark your post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

 

Best Regards,
Community Support Team _ Lin Tu

View solution in original post

1 REPLY 1
Community Support
Community Support

Hi @agneum,

 

Thank you for your feedback on the SQL insert table feature and workaround. This is an issue of the SQL connector, which will be concerned in subsequent development.

It will be useful if anyone is searching for a solution to solve this problem.

You could go ahead and mark your post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

 

Best Regards,
Community Support Team _ Lin Tu

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Users online (10,903)