web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / Best way to search for...
Power Automate
Answered

Best way to search for records in SQL

(1) ShareShare
ReportReport
Posted on by Dextoaar 146
I have a dataverse table where I am taking information from rows (First Name, Last Name, Email, Date of Birth) and trying to see if there is a match in a SQL table that also has all this information. I am wondering if this is something that woud be best to do with Odata filter queries or if there is something I should be considering doing in SQL. 

 
Categories:
Building flows
I have the same question (0)
  • Suggested answer
    trice602 Profile Picture
    trice602 16,305 Super User 2026 Season 1 on at
    Hi,
     
    There are three different avenues to explore.  I would recommend starting off with Odata filter if the dataset is small-to-medium size, SQL for 100,000+ rows.  Power Query if often a forgotten resource but can easily handle data in Dataverse, SQL, and other datasets and do all the transformations and filtering in a single action set.
     

    1. OData Filter Queries in Dataverse

    • Pros: Using OData filter queries can be convenient if you want to handle the comparison within Dataverse and avoid needing additional connections or SQL knowledge. You can set up a filter query to look for matching rows and pull in only what you need.
    • Cons: Performance may become an issue if your Dataverse table has a large number of records, as OData filters can be slower in handling large datasets or complex joins. Additionally, OData isn’t inherently designed for cross-database querying, so you’d be pulling all SQL records in memory before comparing.

    2. Direct SQL Query

    • Pros: A SQL join can be more efficient, especially with larger datasets, as SQL engines are optimized for these operations. You can use an INNER JOIN or EXISTS clause to filter for matching records based on the fields you need (First Name, Last Name, Email, Date of Birth). If your SQL table is frequently updated, this method would also ensure that you are comparing against the latest data.
    • Cons: This approach requires access to both the SQL and Dataverse data sources in a SQL-compatible environment, which can be more complex to set up.

    3. Data Integration Tool (e.g., Dataflows in Power Query)

    • Pros: Power Platform offers Dataflows, which can facilitate the extraction and transformation of data between SQL and Dataverse. You could load both tables into a single Dataflow and apply transformations to compare records.
    • Cons: This might add additional overhead if your matching needs are frequent, as Dataflows generally work best for periodic synchronization or aggregation tasks.

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


    ⭐ If this was helpful, please like and/or mark as a verified answer to help others find this too!


    Always glad to help! 💯💯💯💯💯

    Tom 

    Follow me on LinkedIn - Thomas Rice, PMP | LinkedIn

     


     

     
     
  • Verified answer
    Pstork1 Profile Picture
    Pstork1 69,560 Most Valuable Professional on at
    If you have access to the Database in SQL then the best method would be to do it in a SQL Stored Procedure.  This would offload the performance hit to SQL and give you comparison capabilities that aren't available in Power Automate OData queries, for example case insensitive search.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
11manish Profile Picture

11manish 245

#2
David_MA Profile Picture

David_MA 216 Super User 2026 Season 1

#3
Valantis Profile Picture

Valantis 212

Last 30 days Overall leaderboard

Featured topics

Known issue with browser automation in Power Automate for Desktop on Google Chrome 146 and 147
Share your ideas and struggles with building Power Automate flows!
Survey: Power Automate mobile app
How to Ask for Help with Your Power Automate Workflow
Flows are not visible for users who sign in with their personal accounts
Start and wait for an approval action
Restore a deleted flow
List of actions cheat sheet for Power Automate Desktop
Welcome to the Power Automate forum!

Product updates

Microsoft Power Platform Community release plans