SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.t1(id int, s varchar(5));
CREATE TABLE dbo.t2(id int);
 
INSERT dbo.t1 (id, s) VALUES (1, 'l=3'), (2, 'len=5'), (3, 'l=3');
INSERT dbo.t2 (id)    VALUES (1), (3), (4), (5);
7 rows affected

-- this fails because SQL Server tries to implicitly
-- convert all s values in t1 _before_ the filter happens

DECLARE @t table(dest varchar(3));
 
INSERT @t(dest) SELECT t1.s 
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id;
Msg 2628 Level 16 State 1 Line 6
String or binary data would be truncated in table 'tempdb.dbo.#B31E2F5D', column 'dest'. Truncated value: 'len'.
Msg 3621 Level 0 State 0 Line 6
The statement has been terminated.

-- the easist fix: declare temp tables/table variables so that
-- all columns match data type / length / precision / collation of source

DECLARE @t table(dest varchar(5));
------------------------------^
 
INSERT @t(dest) SELECT t1.s 
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id;
2 rows affected

-- other fixes include explicit left/substring:

DECLARE @t table(dest varchar(3));

INSERT @t(dest) SELECT LEFT(t1.s, 3) 
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id;
2 rows affected

-- an additional filter:

DECLARE @t table(dest varchar(3));

INSERT @t(dest) SELECT t1.s 
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id
  WHERE LEN(t1.s) <= 3;
2 rows affected

-- or different indexes on the source that cause
-- a plan without the problematic the hash match:

CREATE UNIQUE CLUSTERED INDEX ucix_1 ON dbo.t2(id);

DECLARE @t table(dest varchar(3));
 
INSERT @t(dest) SELECT t1.s 
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id;
2 rows affected