INSERT INTO tableB (orderno,buscode,busunit,custname,engno)
SELECT	a.orderno,buscode,busunit,custname,engno
FROM	tableA a JOIN tableC c
		ON a.orderno=c.orderno
		JOIN tableD d
		ON c.projectno=d.projectno
		AND d.project_status = 'A'
		
UPDATE	b
SET		b.busunit = a.busunit
		,b.custname = a.custname
		,b.engno = a.engno
FROM	tableB b JOIN tableA a
		ON b.orderno = a.orderno
		AND b.buscode = a.buscode

MERGE	tableB AS B
USING	(SELECT	a.orderno,a.buscode,a.busunit,a.custname,a.engno,c.projectno,c.project_name,d.project_status
		FROM	tableA a JOIN tableC c
				ON a.orderno=c.orderno
				JOIN tableD d
				ON c.projectno=d.projectno
				AND d.project_status = 'A'
		) AS A (orderno,buscode,busunit,custname,engno,projectno,project_name,project_status)
		ON
		(B.orderno = A.orderno AND B.buscode = A.buscode)
WHEN NOT MATCHED THEN
	INSERT(orderno,buscode,busunit,custname,engno)
	VALUES(a.orderno,a.buscode,a.busunit,a.custname,a.engno)
WHEN MATCHED THEN
UPDATE
SET		B.busunit = A.busunit
		,B.custname = A.custname
		,B.engno = A.engno;