Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Updating Data In SQL Server With Power Query

with 10 comments

As of the November update of Power Query, it’s now possible to write your own SQL query when you’re moving data out of SQL Server into Excel using Power Query. So I got thinking… if you can write your own SQL query, can you execute any other SQL statement? Can you use Power Query to move data out of Excel and into SQL Server? Well, it turns out you can… with some limitations. This blog post details what I found out while researching this problem.

I started with a simple table in a SQL Server database with two columns, Fruit and Sales, and some data:

image

I then created a new function in Power Query with the following definition:

let

    UpdateFunction = (Fruit, Sales) => 

                        Sql.Database(

                            "MySQLServerInstance", 

                            "PowerQueryTest", 

                            [Query="UPDATE [FruitSales] SET [Sales]=" & Number.ToText(Sales) 

                            & " WHERE Fruit='" & Fruit & "'"])

in

    UpdateFunction

 

As you can see, it takes the name of a fruit and a sales value and updates the appropriate row in the SQL Server table. I then created a new table in Excel with some new fruit sales values:

image

Used this table as the source for another Power Query query, and for each row in this table called the function above:

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    InsertedCustom = Table.AddColumn(Source, "Custom", each UpdateFunction([Fruit],[Sales])),

    #"Expand Custom" = Table.ExpandTableColumn(InsertedCustom, "Custom"

                        , {"Records Affected"}, {"Custom.Records Affected"})

in

    #"Expand Custom"

 

I ran this query, and lo! My table in SQL Server was updated:

image

There are some interesting things to note here though. First, for each row in my Excel table, and each time an UPDATE statement was run, Power Query showed a prompt warning me that it was about to make a change to my database:

image 

Probably the safe thing to do here, I think.

Furthermore, running a Profiler trace showed that each UPDATE statement was run at least twice. In fact, I originally started my tests with an INSERT INTO rather than an UPDATE, and found that since the INSERT INTO was run multiple times I ended up with duplicate rows in my table.

None of the code I’ve showed here should be used in a real application of course, but with some thought (and maybe a few changes to the way Power Query behaves), in the future it might be possible to use Power Query to move data out of Excel as well as in.

Written by Chris Webb

December 9, 2013 at 9:26 pm

Posted in Power Query

10 Responses

Subscribe to comments with RSS.

  1. Maybe Power Query should be renamed Power DataMover&Shaker.

    David Hager (@dhExcel)

    December 9, 2013 at 11:03 pm

  2. Hat dies auf MS Excel | Power Pivot | DAX rebloggt und kommentierte:
    Chris Webb beschreibt in diesem Beitrag, dass es mit Power Query theoretisch möglich ist, Daten im SQL-Server Backend zu aktualisieren. Ist sicher nicht gerade das, was sich die meisten DBA’s wünschen…

    selfservicebi

    December 11, 2013 at 1:15 pm

  3. Interesting stuff I m pretty sure there are it sections out there which would want to disable this

    Dalkeith

    December 28, 2013 at 9:55 am

  4. Hi,

    just played with inserting an EXECUTE Statement after an innocuous SELECT and it worked, but not without requiring me to confirm that I really want to do this, and I can only do it from EDIT mode.
    The purpose is to execute a stored procedure which in turn submits and starts an SSIS package loading the table just prepared by some other PowerQuery query into the database, the Advantage being that you can all do it from Excel and need no SSIS component whatsoever on the Client. I wish this could be done in a “legal” way.

    Gottfried Eder

    June 20, 2014 at 5:55 pm

  5. Maybe this is an old topic but does anyone know why the insert statement runs multiple times? I’ve tried insert directly and then it runs about three times per row and when I do the same thing through power query as a stored procedure i get the following results: if i send one row I get one row inserted; if I send two rows I get four rows inserted and if I send three rows I get nine rows inserted. This far i looks like I get x (number of rows that I want to send to the target-table) ^2 rows in my actual target table.

    If anyone has any theories or can help me with this it would be really appreciated.

    Kristian

    August 25, 2014 at 3:25 pm

    • Hi Kristian, I suspect that the reason is that PQ is trying to validate the query before it runs. In any case, I believe that using PQ to change data in a database (rather than to import data from that database) is not something the PQ Dev team encourage; it may not even be officially supported.

      Chris Webb

      August 25, 2014 at 9:04 pm

      • Maybe you’re right. However, I managed to make it work with only one insert per row. The solution (don’t really know why this works) was to empty the “receiving” table. I took you example from above and when I emptied the table showing the results before I ran the statement it made only one insert. But if I had e.g. three old result-rows showing in the table it made one full (the number of rows I wanted to insert) insert per row in the resulting table. So now I “clean” the result-table after every insert and it works rather well.

        Kristian

        August 26, 2014 at 4:45 pm

      • Very interesting – thanks for the information!

        Chris Webb

        August 26, 2014 at 9:18 pm

  6. Is it just me, or does updating data in SQL seems like a really obvious use case for PQ?
    I always have power users who want to master data in Excel, and use that to update tables in SQL. I really don’t have a good answer for this at the moment; I know you can mash up MDS to achieve this, or have SSIS import a spreadsheet, but both of those have a lot of moving parts, and are a bit messy.
    If PQ could target a SQL table, and present a nice UI, that was effectively backed by an UPSERT or MERGE statement, I’d use that all the time.

    Sam Loud

    October 17, 2014 at 9:43 am

    • Yes, I think it would be useful for Power Query to be able to update data in SQL Server, but I can also understand that it would be very hard to get right and I think the dev team are right to focus on their primary use case of getting data into Excel right now. Maybe in the future though…

      Chris Webb

      October 18, 2014 at 8:58 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,309 other followers

%d bloggers like this: