" ( " not valid in this position, expecting an identifier in MySQL

Question

Today is, 1/30/2022, I have been following along with an #AlexTheAnalyst video. I am on a Mac and using MySQL version 8.0.27. (The video is using windows based SQL Server Workbench) I am stuck! I am trying to creating a temporary table function. MySQL is not liking the # in the table name #PercentPopVaccinated as used in the video. When I remove it and run the function/query without the # I get 0 rows returned. I have researched on stackoverflow etc. and I am not coming up with a solution that I understand. (Newbie here)

I have dropped the table that was created and I am starting over.

I am getting an error when creating the temp table that states MySQL is expecting an identifier after the first " ( ". Anyone else have a similar issue?

Create Table #PercentPopulationVaccinated
(
continent nvarchar(255),
location nvarchar(255),
date datetime,
population numeric, 
new_vaccinations numeric,
RollingVacCount numeric
)

Insert into #PercentPopulationVaccinated
Select dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations
, SUM(cast(vac.new_vaccinations as UNSIGNED)) OVER (Partition by dea.location Order by dea.location, dea.date)
as RollingVacCount
-- (RollingVacCount/population)*100
From project_portfolio.covid_deaths dea
Join project_portfolio.covid_vaccinations vac
    On dea.location = vac.location
    and dea.date = vac.date
where dea.continent is not null
-- order by 2,3

Select *, (RollingVacCount/Population)*100
From #PercentPopulationVaccinated;

Answer 1

So I'd say the underlying problem is that you are watching a video tutorial that is using SQL Server, but you are using MySQL. There are many similarities, but it is not going to be an exact match. For instance, the # sign creates a temporary table in Sql Server, but the # is not valid in MySQL. If you want to use a different database service than the tutorial you are watching is for, you are going to have to translate some concepts for yourself.

Another commenter already posted this link, which indicates the syntax for creating temp tables in MySQL.

https://dev.mysql.com/doc/refman/8.0/en/create-table.html#create-table-temporary-tables