Concatenate a string value from table A to a text field in table B, in a scalable way
The suggestion to have one row per value is good advice. It should be the starting point for any relational database design. To ensure all rows are close together (on the same or at least adjacent pages) you will have to have the clustered index on (ID, Date) (or on (Name, Date) depending on which you've chosen for the PK). Next you'll need to make sure there's room for newly-arriving rows beside their siblings. You can do this in one of two ways. One - by setting PAD_INDEX and FILLFACTOR to reserve that space when the index is created. Two - define the index with little or no free space reserved and rebuild it regularly.

That said, what you're proposing is not silly. As long as VALUES is treated as a single attribute and no query tries to dissect it the design is first normal form.

Why would you write to table1 then try to re-write into table2? I'd upsert (i.e. MERGE) into table2 directly, as each value comes into the system.

It seems your primary concern is that the target table is being locked, preventing concurrent work from proceeding. This is caused by lock escalation. The solution is to process the writes in smaller batches. The optimal batch size can only be found by experimentation, but usually a few thousand at a time is about right. I've an answer on this here.

The MERGE statement has had some problems. I haven't checked if they've been fixed recently (&amp; I'm not sure what version you're running) so be aware and read up on the work-around. I propose the following alternative using newer functions which may simplify syntax:

declare @Target table (ID int, x varchar(99));
declare @Source table (ID int, x varchar(99));

insert @Target values (1, 'a'), (2, 'b,c'), (3, 'd');
insert @Source values (1, 'w'), (2, 'x'), (4, 'y');

-- UPDATE @Target with these rows:
select
    t.ID,
    CONCAT_WS (',', t.x, s.x)
from @Target as t
inner join @Source as s    -- INNER JOIN ensures there's a row in both tables
    on s.ID = t.ID

-- INSERT @Target with these rows:
select
    s.*
from @Source as s
where not exists     -- ensures only new rows are attempted
(
    select 1
    from @Target as t
    where t.ID = s.ID
);


Wrap this in the batching technique I suggested above. The downside is a second scan of the data. As the INSERT and UPDATE branches are disjoint sets they could be run in separate, concurrent jobs.