Get DATA using temporary table from SQL

[Anonymous]

Hi, I am trying to execute a sql query and receiving the syntax:  (SQL is created a temporary table.)

"microsoft sql incorrect syntax near the keyword 'into'. incorrect syntax near ')'. power bi

Any help would be appreciated.

tks/

[Anonymous]

I am unsure if my remark will be read but I wanted to discover the most efficient way to pull data that uses a SQL Server QUERY  to pull  data from a Temporary table in Power Bi and use incremental refresh too as the report will be updated daily.  I suppsoe going the stored procedure route is the best?  Any advice is certainly welcome as this is a very large and unruly dataset!

[Fowmy]

@Anonymous 

Seems like you have a syntax error in your SQL code, please re-check.

[Anonymous]

I agree, but the code works in SSMS, but won't pull in PBI.  

part of code:

SELECT DISTINCT ll.LoadId

INTO #LoadFilter

[code]

DROP TABLE #LoadFilter

PBI  does not like the 'INTO' .  Not a SQL expert nor PBI expert, but know a little to understand the error.

[themistoklis]

@Anonymous 

It seems that PowerBI cannot translate the SQL Query properly.

Instead of temp table why dont you use a CTE (in memory table) in your sql query??

Another option is to create a store procedure, add the sql query code there and then call the procedure from PowerBI. Like:

DECLARE @sqlcode varchar(1000)

SET @sqlcode= 'dbo.queryproc
EXEC (@sqlcode)

[Anonymous · Accepted solution]

Figured it out.  Thank you both for your help.

I was trying to do a 'Direct Query' vs 'Import Query'.

Hope you are safe and well... thanks again!😊

[v-yiruan-msft]

Hi @Anonymous ,

Is there anything else need help about this thread? If no, could you please mark your post as Answered? It will help the others in the community find this solution easily if they face the same problem with you. Thank you.

Otherwise, you can refer the following links to get it.

Connect using advanced options

Working with SQL Server data in Power BI Desktop

Best Regards