CODEWITHSUNDEEP
sqlfunctionpowerquery
Sergiy Razumov
Sergiy Razumov

Reputation: 159

Sending to SQL several sequential Queries using Power Query

dear brothers-in-codes =)

By now i'm using Power Query for quite a long time to communicate with SQL Server. I already now how to sent data from Excel Table to SQL Table using INSERT... , UPDATE... , DELETE... instructions.

Yet currently i faced a problem, when i was trying to

  1. Create table
  2. Populate it with the data from Excel Table.

I understood that just have no idea, how to send 2 sequential Queries with

  1. CREATE SCHEMA... & CREATE TABLE... instructions
  2. with INSERT INTO... instruction, passing to this Query Table Columns as parameters

Currently my PQ Function looks like this (the commented part is something that I do manually in SSMS before launching the PQ Function from Excel):

(mCourses, mSQLCourseCode, mOwnToolkit, mTuition, mToolkit, mTuitionDeposit, mInstalments, mAcadDuration, mType, mDateOfUpload)=>

let
    Source = Sql.Database("<a.d.re.ss>", "<DB.Name>", 

[Query="

use EnvirotechFinance

--GO
--if schema_id('price') is null
--execute('CREATE SCHEMA price');
--GO

--drop table if exists price.Price

--Create table price.Price 
--(
--  Courses             nvarchar(255),
--  SQLCourseCode       nvarchar(255),
--  OwnToolkit          int,
--  Tuition             decimal(12,5),
--  Toolkit             decimal(12,5),
--  TuitionDeposit      decimal(12,5),
--  Instalments         int,
--  AcadDuration        int,
--  Type                int,
--  DateOfUpload        date
--)


insert into price.Price 
(       Courses,
        SQLCourseCode,
        OwnToolkit,
        Tuition,
        Toolkit,
        TuitionDeposit,
        Instalments,
        AcadDuration,
                Type,
                DateOfUpload
                        )
Values ('" & mCourses &"', '"& mSQLCourseCode &"', '"& mOwnToolkit &"', '"& mTuition &"', '"& mToolkit &"', 

'"& mTuitionDeposit &"','"& mInstalments &"', '"& mAcadDuration &"', '" & mType & "', '" & mDateOfUpload & "')

"])
in 
    Source

Upvotes: 0

Views: 256

Answers (1)

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

You could make another step which depends on the Source step that also calls Sql.Database with the second query.

However, using Power Query in this fashion is highly discouraged. Native queries should be used for complex read queries or stored procedures, but they should not be used for modifying the database itself. In other words, running INSERT or DELETE operations in Power Query should be avoided. This is because Power Query may re-evaluate the query multiple times, so you can end up inserting a row several more times than you expected.

Upvotes: 1

Related Questions