SQL statement for combining two mssql tables

I. Problems

Student table: course schedule:

Id name course number (foreign key) course number, course name

'1', 'xix ', 1 1, 'China'
'2', 'cic ', 2 2, 'mat'
'3', 'ddi ', 4 3, 'English'

Combine the student table and curriculum into a new table destTb:

Id name course No. Course name

1 xix 1 Language
2 cic 2 mathematics
3 ddi NULL
NULL 3 English

2. Establish Test Data

Create table student (id nvarchar (10), name nvarchar (10), cno int)
INSERT student SELECT '1', 'xix ', 1
Union all select '2', 'cic ', 2
Union all select '3', 'ddi ', 4
GO

Create table class (cno int, name nvarchar (10 ))
INSERT class SELECT 1, 'China'
Union all select 2, 'mat'
Union all select 3, 'English'
GO

Select id, s. name as name, c. cno as cno, c. name as course FROM student as sFULL OUTER JOINClass as cONS. cno = c. cno

3. Merge and insert

--The target table destTb does not exist., The result set is tmp

Select * into destTb from(Select id, s. name as name, c. cno as cno, c. name as course FROM student as s full outer join class as c ON s. cno = c. cno)As tmp

-- If the target tableDestTb already exists

Insert into destTbSelect id, s. name as name, c. cno as cno, c. name as course FROM student as s full outer join class as c ON s. cno = c. cno