cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
agneum
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
v-litu-msft
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
v-litu-msft
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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,916)