How to get current month and year of the database as a macro in SQL server?
Use the DATEPART and/or DATENAME functions.
If you want to reuse, you can create a function to return a string.

DECLARE @Updated VARCHAR(20)

-- numeric month
SET @Updated = 'Product_' + CONVERT(VARCHAR, DATEPART(MONTH, GETDATE())) + CONVERT(VARCHAR, DATEPART(YEAR, GETDATE()))
PRINT @Updated


Product_112018

-- 3 letter month
SET @Updated = 'Product_' + CONVERT(VARCHAR, LEFT(DATENAME(MONTH, GETDATE()), 3)) + CONVERT(VARCHAR, DATEPART(YEAR, GETDATE()))
PRINT @Updated


Product_Nov2018