Hi Brian,

You need to construct the SQL statement and then run it, without seeing you data I can't say anything more precise.

You need a connection to the SQL database which I guess you already have.  Then you need to construct the SQL INSERT or UPDATE using the data from Excel/PQ.

Please provide your workbook and data and a description of the table (column names, data types) within SQL that you want to alter, and what data from Excel/PQ you want to insert into each column.

Are you inserting rows or updating?

Regards

Phil

To keep it very simple and uncomplicated, let’s say that I have a price list in a SQL database.  With PQ, I extract 2 fields, the item code and the price.

In PQ, I say, “If 1^st letter of item code = A, then multiply price by 1.1%, else multiple price by 1.15%.

Now I want to write those 2 fields back to the SQL table.

I want this to be periodic automated function for the end user, with no intervention on my part, so I do not want the SQL UPDATE to be run as a separate exercise.

My question is how do I embed the UPDATE script into my PQ?

Hi Brian,

I can't write a SQL UPDATE without knowing things like your table and column names so here's some code that works on the AdventureWorks sample database running on my PC.  It updates the Order Quantity of a sale to a random number between 1 and 10.

You can modify it to suit your database structure

let
  Source = Sql.Database("PGTW10PC01SQLEXPRESS", "AdventureWorks2017"),
  SqlUpdate = Value.NativeQuery(Source, "UPDATE SALES.[SalesOrderDetail] SET OrderQty=" & Text.From(Number.Round(Number.RandomBetween(1,10))) & " WHERE SalesOrderDetailID = 1;")

in
  SqlUpdate

Once your query has selected the data from the db and done its calculations, you can then write back the updated value. 

You don't need to write the Item Code back to the db, it hasn't changed?

Make sure you wrap the price in Text.From() to convert it to text - the SQL string must be all text.

You probably want this to only run under certain conditions, not every time queries are refreshed? In which case you can use an IF ... THEN ... ELSE to check for a condition under which to run the SQL update.

Regards

Phil

Nearly forgot to mention that you will need to make a change to the PQ Options otherwise every time the query runs PQ will ask you to give permission to run the query.

In the PQ Editor -> File -> Options and settings -> Query Options -> Security

Under Native Database Queries, uncheck 'Require user approval for new native database queries'

Please understand that this will allow SQL to run against your database and modifications to the db can be made so you need to make sure that the account being used only has sufficient privileges.

Regards

Phil