looking for a cleaner way to select from multiple tables
First, I would remove the Order by weekrange desc as it is unnecessary (since you aren't also using TOP() in any of the SELECT queries.

Also, why use UNION ALL in the first place? Why not just have these be 3 - 12 separate INSERT INTO table SELECT .... queries?

And, given that the only thing changing in each query is the name of the table, and that name follows a pattern, the dynamic query can be templated and then just loop through the number of months you need.

The following should do what you want, with each INSERT...SELECT from each separate SMD_ table being a separate query / transaction:

DECLARE @Search NVARCHAR(10) = N'_search_', -- just to get the test code working
       @Store NVARCHAR(10) = N'_store_'; -- just to get the test code working

DECLARE @Now DATETIME2;
SET @Now = SYSDATETIME();

DECLARE @QueryTemplate AS NVARCHAR(MAX)
SET @QueryTemplate = N'
INSERT INTO SMDTemp (QDATE, UPC2, AWEEK, WEEKRANGE, MOVEMENT, COST, RTL, LBS, GP) 
  SELECT @Now_tmp AS [QDATE], UPC2, MAX(RTG_WKD.AWEEK), RTG_WKD.WEEKRANGE,
         SUM(SMVNUM) AS [MOVEMENT], ROUND(AVG(SMVCOST), 2) AS [COST],
         ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS [RTL],
         CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS [LBS],
         (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE
             WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL
              ELSE round(AVG(smvsugs / smvsugns), 2) END AS [GP]
  FROM SMD_{{TableDate}}
  INNER JOIN RTG_WKD
          ON SMVDATE = RTG_WKD.WKDDATE6
  WHERE UPC2 = @Search_tmp
  AND   SMVStore = @Store_tmp
  GROUP BY RTG_WKD.WEEKRANGE, UPC2;
';

DECLARE @SQLQuery NVARCHAR(MAX);
SET @SQLQuery = N'';

DECLARE @MonthsBack INT;
SET @MonthsBack = 0;

WHILE (@MonthsBack &gt; -12)
BEGIN
  SET @SQLQuery = @SQLQuery + REPLACE(@QueryTemplate,
                                      N'{{TableDate}}',
                                      REPLACE(CONVERT(NVARCHAR(15),
                                                      DATEADD(MONTH,
                                                              @MonthsBack,
                                                              GETDATE()),
                                                      102),
                                              N'.',
                                              N'_')
                                              )
                            + NCHAR(0x0D) + NCHAR(0x0A);

  SET @MonthsBack = @MonthsBack - 1;
END;

PRINT @SQLQuery; -- debug

EXEC sp_executesql
  @SQLQuery, -- define the query
  N'@Now_tmp DATETIME2, @Search_tmp VARCHAR(100), @Store_tmp VARCHAR(100)', --param list
  @Now_tmp = @Now, -- pass in parameter: param_name = local_variable_name
  @Search_tmp = @Search, -- pass in parameter: param_name = local_variable_name
  @Store_tmp = @Store; -- pass in parameter: param_name = local_variable_name


This query was originally using EXEC(@SQLQuery) but has been parameterized to use sp_executesql (as mentioned by @Jean in a comment on the Question).