Union tables and fill null with last not null

14 
WITH AllData
AS
(
	SELECT [Start_Date], [Name], qty, [Location] FROM #Table1
	UNION ALL
	SELECT [Start_Date], [Name], qty, [Location] FROM #Table2
)
SELECT [Start_Date], [Name], qty
	,COALESCE([Location], LAG([Location]) OVER (PARTITION BY [Name] ORDER BY [Start_Date])) AS [Location]
FROM AllData;

or if there are multiple consecutive nulls:

WITH AllData
AS
(
	SELECT [Start_Date], [Name], qty, [Location] FROM #Table1
	UNION ALL
	SELECT [Start_Date], [Name], qty, [Location] FROM #Table2
)
SELECT [Start_Date], [Name], qty
	,COALESCE
	(
		[Location]
		,SUBSTRING
		(
			MAX
			(
				CONVERT(char(8), [Start_Date], 112) + [Location])
					OVER (PARTITION BY [Name] ORDER BY [Start_Date]
					
			)
			,9, 100
		)
	) AS [Location]
FROM AllData;

With SQL 2022 you can also use LAST_VALUE IGNORE NULLS.