Farward fill Null values

Transact-SQL
2

Umm.... You did not even try this test data as it obviously fails the primary key constraint.

As a one off here is how I would define the test data using temp tables and dates in ISO format.
It would also be better to avoid reserved words for column names but I have left them as they were.

CREATE TABLE #t1
(
	[Date] date NOT NULL
	,[Name] varchar(20) NOT NULL
	,qty int NOT NULL
	,[Location] varchar(20) NULL
	,PRIMARY KEY ([Name], [Date])
);
INSERT INTO #t1
VALUES ('20220101','Apple',2,'1st row')
	,('20220101','Orange',10,'2nd row')
	,('20220102','Apple',5,NULL)
	,('20220102','Kiwi',1,'10th row');

CREATE TABLE #t2
(
	[Date] date NOT NULL
	,[Name] varchar(20) NOT NULL
	,qty int NOT NULL
	,[Location] varchar(20) NULL
	,PRIMARY KEY ([Name], [Date])
);
INSERT INTO #t2
VALUES ('20220103','Orange',9,NULL)
	,('20220104','Apple',10,'3rd row')
	,('20220105','Kiwi',4,NULL)
	,('20220105','Apple',2,'5th row')
	,('20220106','Apple',5,NULL);

select * from #t1;
select * from #t2;

So what exactly do you think is wrong with the outline solution in the previous thread?

Using this test rig:

WITH AllData
AS
(
	SELECT [Date], [Name], qty, [Location] FROM #t1
	UNION ALL
	SELECT [Date], [Name], qty, [Location] FROM #t2
)
SELECT [Date], [Name], qty
	,[Location] AS Original_Location
	,COALESCE([Location], LAG([Location]) OVER (PARTITION BY [Name] ORDER BY [Date])) AS New_Location
FROM AllData
ORDER BY [Name], [Date];

As also pointed out, the problem with this solution occurs if there are two or more consecutive rows with NULL in Location.
To see this, try the above query again after adding:

INSERT INTO #t2
VALUES ('20220103','Apple',5,NULL)
	,('20220107','Apple',5,NULL);

One way around the consecutive nulls problem is:

WITH AllData
AS
(
	SELECT [Date], [Name], qty, [Location] FROM #t1
	UNION ALL
	SELECT [Date], [Name], qty, [Location] FROM #t2
)
SELECT [Date], [Name], qty
	,[Location] AS Original_Location
	,COALESCE
	(
		[Location]
		,SUBSTRING
		(
			MAX(CONVERT(char(8), [Date], 112) + [Location])
				OVER (PARTITION BY [Name] ORDER BY [Date])
			,9, 100
		)
	) AS New_Location
FROM AllData
ORDER BY [Name], [Date];