SQL Sever Query With Temp tables will not import - Errors dont align

[mstarback]

Hello, When I attempt to import this into Power BI it gives an error stating "Microsoft SQL: Incorrect syntax near the keyword 'CREATE'. Incorrect syntax near ')'. 

This query runs perfect in SQL Server and I am able to get the second temp table #Sprocket imported just fine. THe issue seems to be with #ISM. But nothing is jumping out at me. I attempted to use Select * into #ISM as well. Is powerBI capable of handling two temps in one import? THank you for any help.

CREATE TABLE #ISM(
Users INT,
Proj_Number VARCHAR(100),
Proj_Name VARCHAR(100))
INSERT INTO #ISM
SELECT
COUNT(U.[personid]) AS Users
,P.Proj_Number,
P.Proj_name
FROM [A].[USERS] AS U
JOIN A.Projects AS P
ON P.project_seq_id = U.project_seq_id

--WHERE P.proj_number LIKE '150666'
GROUP BY
P.Proj_Number,
P.Proj_name

CREATE TABLE #Sprocket(
Users INT,
SiteName VARCHAR (100),
Today DATETIME,
Last_Update DATETIME,
Days_Since_Update INT)

INSERT INTO #Sprocket
SELECT

COUNT(DISTINCT R.UserName) AS Users
,R.[SiteName]
,GETDATE() AS Today
,MAX(LogDate) AS 'Last_Update'
,DATEDIFF(dd,MAX(LogDate),GETDATE()) AS Days_Since_Update

FROM [B].[S].[Roster] AS R
JOIN [B].[S].[BadgeNumberLog] AS BNL
ON BNL.UltiProID = R.UltiProID
WHERE OnRoster = 1

GROUP BY
[SiteName]

SELECT
S.Users AS Sprocket_Users,
SiteName,
Today,
Last_Update,
CASE WHEN Days_Since_Update IS NULL THEN 0
ELSE Days_Since_Update
END AS Days_Since_Update,
I.Users AS ISM_Users,
Proj_Number,
Proj_Name,
CASE
WHEN (S.Users) < I.Users THEN 'Sprocket Has Less Users'
WHEN S.Users > I.Users THEN 'ISM Has Less Users'
WHEN S.Users = I.Users THEN 'MATCH'
WHEN SiteName IS NULL THEN 'Project Not In Sprocket'
ELSE ''
END AS User_Troubleshooting,
CONCAT('Hasnt been updated in ', ISNULL(Days_Since_Update,0), ' Days.') AS Days_Since_Update_Text

FROM #ISM AS I
LEFT OUTER JOIN #Sprocket AS S
ON S.SiteName = I.Proj_Number

[dax · Accepted solution]

Hi @mstarback , 

I test this in  my environment with below code, which work well 

Create table #tt1 (
name varchar(20), id int)
insert into #tt1 values('a', 1), ('b',2)

Create table #tt2 (name varchar(20), id int)
insert into #tt2 values('a1', 1), ('b1',3)

select a.name, a.id,b.name  from #tt1 a left join #tt2 b on a.id=b.id 

So I think two temp tables should work. You could try to test below to see whether it work or not. Or you also could use create a stored procedure, then use it in powerbi to see whether it work or not.

CREATE TABLE #ISM(
Users INT,
Proj_Number VARCHAR(100),
Proj_Name VARCHAR(100))
INSERT INTO #ISM
SELECT
COUNT(U.[personid]) AS Users
,P.Proj_Number,
P.Proj_name
FROM [A].[USERS] AS U
JOIN A.Projects AS P
ON P.project_seq_id = U.project_seq_id

--WHERE P.proj_number LIKE '150666'
GROUP BY
P.Proj_Number,
P.Proj_name

select *  from #ISM

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.