All you have to set / pass in is the "@number_of_months", although you could pass in a different ending month if you wanted to.

DECLARE @number_of_months...

OPENQUERY accepts only a literal string query (review the syntax for OPENQUERY() to confirm this).

You have to use dynamic SQL if you want to build a dynamic OPENQUERY...

Yes, the trigger will fire again.

The easiest way around that is to use CONTEXT_INFO to set a "flag byte" to "tell" the trigger not to update on the...

This looks like it might just be some type of bug.  Do you have the latest svc pack for that SQL applied?

What you need/want is a CROSS JOIN.  That will match every ID with every year:

SELECT n.ID, y.year
FROM dbo.Name n
CROSS JOIN (
    SELECT...

The hard task, that's not trivial, is changing to use a sophisticated, automated scripting mechanism to push the same script out to hundreds of databases, or a selected sub-set of...

But the OP doesn't have all that in place, and, as I said before,  it's NOT trivial to do.

Ssometimes the most practical solution is the best. By way...