Overview
Power Automate enables seamless integration with SQL databases, allowing businesses to automate workflows, sync data, and trigger actions based on database events. With Power Automate + SQL Server, you can:
Automate data entry and updates
Trigger workflows based on database changes
Sync SQL data with Microsoft 365 apps (Excel, SharePoint, Outlook, Teams, Power BI, etc.)
Generate reports and notifications
Perform CRUD (Create, Read, Update, Delete) operations on SQL tables
1️⃣ Benefits of Connecting Power Automate with SQL Server
Automates Database Tasks – No manual data entry required.
Real-Time Data Sync – Keep databases and applications up to date.
Triggers and Actions – Automate workflows based on SQL table changes.
Multi-Platform Integration – Connect SQL databases with cloud and on-premises systems.
Enhanced Reporting – Use SQL data in Power BI for real-time insights.
2️⃣ Setting Up Power Automate with SQL Server
Requirements
✔ Power Automate License (included in Microsoft 365 or standalone)
✔ SQL Server (Azure SQL, On-Prem SQL, or SQL Managed Instance)
✔ SQL Server Connector in Power Automate
✔ On-Premises Data Gateway (for local databases)
How to Connect Power Automate to SQL Server
1️⃣ Go to Power Automate
2️⃣ Click “Create” → Select “Automated cloud flow”
3️⃣ Choose a SQL Server trigger (e.g., “When a row is inserted”)
4️⃣ Sign in with your SQL Server credentials
5️⃣ Add actions to perform SQL queries or interact with other apps
6️⃣ Save and Test the Flow
Example: When a new order is added to SQL, send an email notification.
3️⃣ Power Automate Triggers for SQL Server
| Trigger Name | When It Runs | Example Use Case |
|---|---|---|
| When a row is inserted | A new row is added to a SQL table | Notify sales team when a new order is placed |
| When a row is modified | An existing SQL record is updated | Send an alert when inventory levels are low |
| When a row is deleted | A record is removed from SQL | Log deleted records for auditing |
| On-Premises SQL Trigger | A change occurs in an on-prem SQL database | Sync local database changes with cloud apps |
Example:
Trigger: “When a new customer is added”
Flow: Sync the new customer details with Dynamics 365 and SharePoint.
4️⃣ Power Automate Actions for SQL Server
| Action Name | Purpose | Example Use Case |
|---|---|---|
| Get rows | Retrieve multiple records from SQL | Export data to Excel |
| Get row by ID | Retrieve a specific record | Find an order by Order ID |
| Insert row | Add a new record to a SQL table | Store form submissions in SQL |
| Update row | Modify an existing record | Update inventory levels from Power Apps |
| Delete row | Remove a record from SQL | Auto-delete old log entries |
| Execute a SQL query | Run a custom SQL command | Generate a report or perform bulk updates |
Example:
Action: “Insert a row”
Flow: Capture Microsoft Forms submissions and store them in a SQL database.
5️⃣ Automating Workflows with Power Automate + SQL Server
Example 1: Syncing SQL Data with SharePoint
Scenario: When a new project is added to SQL, automatically create a SharePoint record.
1️⃣ Trigger: “When a row is inserted in SQL”
2️⃣ Action: Create a SharePoint list item
3️⃣ Action: Send a Teams notification to the project team
Use Case: Keep SQL and SharePoint in sync without manual entry.
Example 2: Sending Email Alerts for Critical Database Changes
Scenario: Notify the finance team when a high-value transaction is recorded in SQL.
1️⃣ Trigger: “When a row is inserted in SQL”
2️⃣ Condition: “If transaction amount > $10,000”
3️⃣ Action: Send an Outlook email notification
4️⃣ Action: Log the transaction in Dataverse
Use Case: Real-time fraud detection and financial monitoring.
Example 3: Syncing SQL Inventory Data with Power Apps
Scenario: When a warehouse manager updates stock levels in Power Apps, update SQL automatically.
1️⃣ Trigger: “When an item is updated in Power Apps”
2️⃣ Action: Update the SQL inventory table
3️⃣ Action: Send a notification to the procurement team
Use Case: Ensure up-to-date stock information across systems.
Example 4: Exporting SQL Data to Excel for Reporting
Scenario: Extract customer order data from SQL and generate a daily Excel report.
1️⃣ Trigger: Scheduled (every morning at 8 AM)
2️⃣ Action: Retrieve SQL order records
3️⃣ Action: Store data in an Excel file in OneDrive
4️⃣ Action: Email the report to sales managers
Use Case: Automate report generation without manual exports.
Example 5: Syncing SQL Data with Power BI for Real-Time Dashboards
Scenario: Extract real-time sales data from SQL and refresh Power BI dashboards.
1️⃣ Trigger: Scheduled (every hour)
2️⃣ Action: Retrieve SQL sales records
3️⃣ Action: Store data in Dataverse or Azure Data Lake
4️⃣ Action: Refresh Power BI dataset
Use Case: Enable real-time sales analytics in Power BI.