Good day <?!?>
If I understand your request correctly, then you want to CONVERT NVARCHAR into VARCHAR. This means that you might lose data, since Unicode includes 1,114,112 code positions and maps over 100k different characters while extended ASCII only include 256 code positions, which mean it cannot map more then 256 different characters!
Once you took this into consideration you might find the following trick helpful
/********************************************************* */
/**************************** CONVERT FROM Unicode to Ascii */
/********************************************************* */
--------------------------------------- Demo table (DDL+DML)
DROP TABLE IF EXISTS T
GO
create table T(txt nvarchar(100)) -- Unicode
GO
INSERT T(txt) VALUES (N'רונן ronen') -- Hebrew & English Characters (my First name)
GO
SELECT * FROM T
GO
--------------------------------------- Solution Using temp table
DROP TABLE IF EXISTS #T;
CREATE TABLE #T (txt VARCHAR(200) COLLATE Hebrew_CI_AI)
--INSERT T_NEW (txt) SELECT CONVERT (VARCHAR(200), txt COLLATE Hebrew_CI_AI)
INSERT #T (txt) SELECT txt
FROM T
SELECT* FROM T
SELECT* FROM #T
GO
