See the comment from @PIEBALDconsult - do not use a cursor to do this.
If you want to create a new table from your query then simply use
SELECT COUNT([Date]) AS Modified, Year([Date]) [Year],MONTH([Date]) [Month]
INTO NewTable
FROM TableName
GROUP BY Year([Date]),MONTH([Date])
If
NewTable
already exists then do it this way instead
INSERT INTO NewTable
SELECT COUNT([Date]) AS Modified, Year([Date]) [Year],MONTH([Date]) [Month]
FROM TableName
GROUP BY Year([Date]),MONTH([Date])
Also not the square brackets
[ ]
around the column names
Date
and
Month
- this is because those words are reserved words in SQL - if you really cannot think of anything else to call the column you must indicate to SQL that you really mean the column name and not the reserved word.