This is working for me and it is based on modified version of this:

Is there a way to loop through a table variable in TSQL without using a cursor?

As you can see, no more Cursors! HTH someone!

DECLARE @target_state CHAR(4) 
DECLARE @geom_state geography
DECLARE @distance FLOAT = 482803; -- A distance in metres   482803 = 300 miles
declare @RowNum int, @State_ID int
select State_ID=MAX(State_ID) FROM [dbo].[gis_US_States_GeoJSON]     --start with the highest ID
Select @RowNum = Count(*) From [dbo].[gis_US_States_GeoJSON]       --get total number of records
WHILE @RowNum > 0                          --loop until no more records
BEGIN   
    select  @target_state = STUSPS, @geom_state = State_Boundaries_geog
    from [dbo].[gis_US_States_GeoJSON] where State_ID= @RowNum 
    INSERT INTO dbo.gis_shell300_imls (MID)(SELECT m.MID 
       FROM dbo.gis_MuseumFile2018_File1_Nulls m , dbo.gis_2022_gaz_zcta_national z
      WHERE
       @geom_state.STContains(z.ZCTA_Centroid_Geography)  = 1)
    set @RowNum = @RowNum - 1    --decrease count
END