Quick question to my friends in the flow forum, I ended up making a stored procedure to lifecycle some SQL data that needed to be overlayed with an insert or delete insert scheme, to insure no duplicates are in the table on insertion. I used a stored proc to do a classic clean before insert, since the two step flow I had envisioned would not work. I used the SQL delete record, then insert record and they work fine, however, there is a pesky artifact of the RecordID that I may be ignorant to using. My key is two fields, a date and a small text record type. The RecordId can certainly contain two fields from the dynamic content picker, however it always errors out with some message that "the key contains two values and you only supplied one" So...I tried every delimiter I could come up with, checked google, and am left to believe that with SQL delete flow step, the simple primary key is the only supported paradigm. Can this "dynamic content" express a two field primary key, or am I pushing the limits? Either way, Thankyou Microsoft for letting me run a stored procedure from Flow so I could do my own data manips from my world !!!!!
PS - I absolutely LOVE being able to select a flow execution instance, then hopping through the steps to see not only what data I passed, but what the execution status was at each point in the decision tree. It's like a case statement that is in debug with visual studio after the run is over! (Wow, for as much as we developers complain, you guys are smart!)
OK, replying to myself is frankly creepy, but I will do it to document this for my silent friends in the land of flow.
I solved this by enhancing the stored procedure and passing the duplicate key fields into the SQL. From there I wipe the old stuff and delete the new stuff, effectively passing on the clustered primary key construct to the sql to deal with it.
To the powers at Microsoft, if a primary key in SQL server can be multiple columns, we need a way in the flow tool to reference this, not just a single numeric ID for the key. I'm thinking this is an upcoming feature, but at this point I am talking to myself, so I will shut up!
So long, (and thanks for all the flows!)
I am glad to hear that you have solved this issue.
Currently, Flow doesn’t support a primary key in SQL server with multiple columns, you may consider to submit an idea at Flow ideas Forum, I will help collect and report it on my side.
Thanks @Anonymous much appreciated! I will remember this next time I run into that two part key situation. Best wishes and stay healthy out there!
@Anonymous I followed your suggestion but I got an error: Item with id '2020-09-01T10:00:00Z,Appraisals,20200901' does not exist.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Read the latest about new experiences and capabilities in the Power Automate product blog.
If you are a small business ISV/Reseller, share your thoughts with our research team.
The AI Builder team invite you to ask questions and provide helpful answers at our next AMA.