Two tables of data integration

oohhss

if object_id ('a') is not null drop table a
go
CREATE TABLE a (
       [teacherid] [varchar] (50)
       [courseinfo] [varchar] (50),
       [coursename] [varchar] (50),
       [point] [float],
      
 )

insert into a (courseinfo, coursename, point, bjjh) values ​​('0001', '0101011002_1_1', 'Magma and Metamorphic Petrology_Theory', 80.05)
insert into a (courseinfo, coursename, point, bjjh) values ​​('0002', '0101011003_1_1', 'Magma_Theory', 90.50)
insert into a (courseinfo, coursename, point, bjjh) values ​​('0003', '0101011006_1_1', 'Metamorphic Petrology_Theory', 90.50)
insert into a (courseinfo, coursename, point, bjjh) values ​​('0004', '0101011007_1_1', 'Geographic Informatics_Theory', 90.50)
go

if object_id ('b') is not null drop table a
go
CREATE TABLE b (
       [teacherid] [varchar] (50)
       [courseinfo] [varchar] (50),
       [coursename] [varchar] (50),
       [point] [float],
      
 )

insert into a (courseinfo, coursename, point, bjjh) values ​​('0001', '0101011002_1_1', 'Magma and Metamorphic Petrology_Theory', 83.05)
insert into a (courseinfo, coursename, point, bjjh) values ​​('0003', '0101011006_1_1', 'Metamorphic Petrology_Theory', 95.50)
insert into a (courseinfo, coursename, point, bjjh) values ​​('0005', '0101011008_1_1', 'Geographic Informatics_Theory', 90.50)
go

The data in the above two tables want to be integrated into one table. In table a, there are teachers and their scores, but in table b, there is no one. Table b automatically adds one to table a, but the teacher's point item is 0 points, and the same b The table has the same treatment as table a.

sinexe

do not understand

八宝斋

insert into a
select * from b where courseinfo not in (select courseinfo from a)

hiqwolfzzh

Should the sql provided by lz be modified to:

CREATE TABLE a (
       [teacherid] [varchar] (50),
       [courseinfo] [varchar] (50),
       [coursename] [varchar] (50),
       [point] [float]
      
 )

insert into a (teacherid, courseinfo, coursename, point) values ​​('0001', '0101011002_1_1', 'Magma and Metamorphic Petrology_Theory', 80.05)
insert into a (teacherid, courseinfo, coursename, point) values ​​('0002', '0101011003_1_1', 'Magma_theory', 90.50)
insert into a (teacherid, courseinfo, coursename, point) values ​​('0003', '0101011006_1_1', 'Metamorphic Petrology_Theory', 90.50)
insert into a (teacherid, courseinfo, coursename, point) values ​​('0004', '0101011007_1_1', 'Geographic Informatics_Theory', 90.50)
go

CREATE TABLE b (
       [teacherid] [varchar] (50),
       [courseinfo] [varchar] (50),
       [coursename] [varchar] (50),
       [point] [float]
      
 )
insert into b (teacherid, courseinfo, coursename, point) values ​​('0001', '0101011002_1_1', 'Magma and Metamorphic Petrology_Theory', 83.05)
insert into b (teacherid, courseinfo, coursename, point) values ​​('0003', '0101011006_1_1', 'Metamorphology_Theory', 95.50)
insert into b (teacherid, courseinfo, coursename, point) values ​​('0005', '0101011008_1_1', 'Geographic Informatics_Theory', 90.50)
go

hiqwolfzzh

insert into a
select teacherid, courseinfo, coursename, point 0
from b
where teacherid not in (select distinct teacherid from a)

hiqwolfzzh

I just wrote it wrong, so
insert into a
select teacherid, courseinfo, coursename, 0
from b
where teacherid not in (select distinct teacherid from a)

oohhss

if object_id('a') is not null drop table a
go
CREATE TABLE a (
       [teacherid] [varchar] (50),
       [courseinfo] [varchar] (50),
       [coursename] [varchar] (50),
       [point] [float],
      
)

insert into a(teacherid,courseinfo,coursename,point) values('0001','0101011002_1_1','magmatic rock and metamorphic petrology_theory', 80.05)
insert into a(teacherid,courseinfo,coursename,point) values('0002','0101011003_1_1','magmatology_theory',90.50)
insert into a(teacherid,courseinfo,coursename,point) values('0003','0101011006_1_1','metamorphic petrology_theory',90.50)
insert into a(teacherid,courseinfo,coursename,point) values('0004','0101011007_1_1','geoinformatics_theory',90.50)
go

if object_id('b') is not null drop table a
go
CREATE TABLE b (
       [teacherid] [varchar] (50),
       [courseinfo] [varchar] (50),
       [coursename] [varchar] (50),
       [point] [float],
      
)

insert into a(teacherid,courseinfo,coursename,point) values('0001','0101011002_1_1','magmatic rock and metamorphic petrology_theory', 83.05)
insert into a(teacherid,courseinfo,coursename,point) values('0003','0101011006_1_1','metamorphic petrology_theory',95.50)
insert into a(teacherid,courseinfo,coursename,point) values('0005','0101011008_1_1','geoinformatics_theory',90.50)
go

The statement to generate the table is like this. It was written in a hurry that day and there was no time to verify it. I'm really sorry!

oohhss

if object_id('a') is not null drop table a
go
CREATE TABLE a (
       [teacherid] [varchar] (50),
       [courseinfo] [varchar] (50),
       [coursename] [varchar] (50),
       [point] [float],
      
)

insert into a(teacherid,courseinfo,coursename,point) values('0001','0101011002_1_1','magmatic rock and metamorphic petrology_theory', 80.05)
insert into a(teacherid,courseinfo,coursename,point) values('0002','0101011003_1_1','magmatology_theory',90.50)
insert into a(teacherid,courseinfo,coursename,point) values('0003','0101011006_1_1','metamorphic petrology_theory',90.50)
insert into a(teacherid,courseinfo,coursename,point) values('0004','0101011007_1_1','geoinformatics_theory',90.50)
go

if object_id('b') is not null drop table b
go
CREATE TABLE b (
       [teacherid] [varchar] (50),
       [courseinfo] [varchar] (50),
       [coursename] [varchar] (50),
       [point] [float],
      
)

insert into b(teacherid,courseinfo,coursename,point) values('0001','0101011002_1_1','magmatic rock and metamorphic petrology_theory', 83.05)
insert into b(teacherid,courseinfo,coursename,point) values('0003','0101011006_1_1','metamorphic petrology_theory',95.50)
insert into b(teacherid,courseinfo,coursename,point) values('0005','0101011008_1_1','geoinformatics_theory',90.50)
go

select * from a
select * from b
----The above section of the generated table
---This answer is interesting, but it has not fully answered my question
insert into a
select teacherid,courseinfo,coursename,0
from b
where teacherid not in(select distinct teacherid from a