SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE PS_MASTER_ITEM_EC(INV_ITEM_ID NVARCHAR(10));

INSERT INTO PS_MASTER_ITEM_EC VALUES ('')
1 rows affected

SELECT * FROM PS_MASTER_ITEM_EC

INV_ITEM_ID

SELECT IIF(EC.INV_ITEM_ID <> '', EC.INV_ITEM_ID, s.col)
FROM (SELECT 'NO ITEMS IN STAGING TABLES') s(col)
LEFT JOIN PS_MASTER_ITEM_EC EC ON 1=1
ORDER BY EC.INV_ITEM_ID

(No column name)

NO ITEMS IN STAGING TABLES

TRUNCATE TABLE PS_MASTER_ITEM_EC
SELECT * FROM PS_MASTER_ITEM_EC

INV_ITEM_ID

SELECT IIF(EC.INV_ITEM_ID <> '', EC.INV_ITEM_ID, s.col)
FROM (SELECT 'NO ITEMS IN STAGING TABLES') s(col)
LEFT JOIN PS_MASTER_ITEM_EC EC ON 1=1
ORDER BY EC.INV_ITEM_ID

(No column name)

NO ITEMS IN STAGING TABLES

TRUNCATE TABLE PS_MASTER_ITEM_EC
INSERT INTO PS_MASTER_ITEM_EC VALUES ('abc')
1 rows affected

SELECT * FROM PS_MASTER_ITEM_EC

INV_ITEM_ID

abc

SELECT IIF(EC.INV_ITEM_ID <> '', EC.INV_ITEM_ID, s.col)
FROM (SELECT 'NO ITEMS IN STAGING TABLES') s(col)
LEFT JOIN PS_MASTER_ITEM_EC EC ON 1=1
ORDER BY EC.INV_ITEM_ID

(No column name)

abc