Create Table from Another Table – MYSQL to MSSQL conversion

I’m in the process of converting a database from MYSQL to MSSQL/TSQL, and I can’t get the following MYSQL query to work in MSSQL. Looking for a fresh set of eyes to see what I’m missing.

Working MYSQL Query:

AS (SELECT * FROM Note WHERE createdate IN (SELECT MAX(createdate) 
FROM Note GROUP BY `taskPK`));

The query looks for the max date of a note row for a given id. There may be multiple Note rows with the same ID and it will find the newest row and insert it into the new table.

Non-Working MSSQL Example 1: (Incorrect Syntax near ‘(‘)

AS(SELECT * FROM dbo.Note WHERE createdate IN (SELECT MAX(createdate) 
FROM dbo.Note GROUP BY taskPK));

Non-Working MSSQL Example 2: (Incorrect Syntax near the keyword INTO)

FROM Note 
WHERE createdate IN (SELECT MAX(createdate) FROM Note GROUP BY taskPK) 
INTO tempTable FROM Note;

This new table (once created) will be used in the following query (which I haven’t tested yet in MSSQL):

SELECT, Task.taskdescription, Task.status,,, tempTable.createDate FROM Task 
LEFT JOIN TaskSchedule ON = TaskSchedule.taskPK
LEFT JOIN TaskGroups ON Task.taskgroupid =
LEFT JOIN tempTable ON = tempTable.taskPK
WHERE Task.userPK = '$userPK' AND Task.status = 'Open'
ORDER BY tempTable.createDate DESC;

The above query selects a task that includes the newest note from the previous query I’m having problems with — adding this incase I’m making this more difficult than it needs to be.

Source: stackoverflow-php