A表:
ID int
NAME varchar
sex varchar
B表:
ID int
DATA varchar
NAME varchar
CLASS Varchar
Grade Varchar
teachwer Varchar如何用存储过程实现B表内容插入A表,并能通过视图C看到。有劳了
ID int
NAME varchar
sex varchar
B表:
ID int
DATA varchar
NAME varchar
CLASS Varchar
Grade Varchar
teachwer Varchar如何用存储过程实现B表内容插入A表,并能通过视图C看到。有劳了
select
ID,
Name,
null
from B
where id not in (select id from A)
--查询select A.*,B.*
from A
inner join B on A.id=B.id
as
begin insert tablea(id,name,sex) select id,name,null from tableb a where not exists(select 1 from Tablea where id = a.id)
end
goexec ins
ID int
NAME varchar
sex varchar
B表:
ID int
DATA varchar
NAME varchar
CLASS Varchar
Grade Varchar
teachwer Varchar 如何用存储过程实现B表内容插入A表,并能通过视图C看到。有劳了------------------------------------------------------
sex咋班?
insert into A select id , name , sex = '' from B
insert into A select id , name , sex = '' from B where id not in select id from A
create proc ins
as
begin
insert tablea(id,name,sex) select id,name,'0' from tableb a where not exists(select 1 from Tablea where id = a.id)
end
go
--查看试图
create view C ON A
AS
begin
select id,name from A where sex = '0'
end
go
--执行插入
exec ins
--查看最新未处理插入数据。
select * from C
插入后A表:
ID int
NAME varchar
sex varchar
B_ID int
DATA varchar
B_NAME varchar
CLASS Varchar
Grade Varchar
teachwer Varchar
--建立表A,B
create table a(
Id int,
[Name] varchar(50),
sex varchar(50)
)
create table b(
Id int,
DATA varchar(50),
[Name] varchar(50),
CLASS varchar(50),
Grade varchar(50),
teachwer varchar(50)
)--往B表添加测试数据
insert into b
select '1','1986','yinqi','1班','二年级','不晓得'
go--code
--创建存储过程
create proc #B_To_A
as
--创建视图
declare @str varchar(1000)
set @str='create view V_C
as
select id,[name] as Name,'''' as Sex from b where not exists(select 1 from a where a.id=b.id)'
exec(@str)
go--测试
exec #B_To_A
--通过视图查看A表数据
select * from V_C
go--测试结束
drop table a
drop table b
drop view V_C
drop proc #B_To_A
insert ta
select id,name,null from tb a
left join ta b
on a.name=b.name
where b.name is null2
至于视图能看到,不知道你说什么
插入后A表:
ID int
NAME varchar
sex varchar
B_ID int
DATA varchar
B_NAME varchar
CLASS Varchar
Grade Varchar
teachwer Varchar 那lz你就先要把a表通过alter table add column 先加列啊!!!!
然后再写入存储过程
insert into a(id,name,sex,b_id,data,b_name,class,grade,teacher)
select '',name,sex,id,data,b_name,class,grade,teacher from b where id not in (select id from a)
当然如果你想把加列的过程也加进存储过程,那请你在add column前加入判断if exists (select * from syscolumns where id=object_id('a') and name='b_name')
begin
alter table a
add
B_ID int ,
DATA varchar(50) ,
B_NAME varchar(50),
CLASS Varchar(50),
Grade Varchar(50),
teachwer Varchar (50)end不过个人认为没什么意思,还有你要在视图c显示什么?说清楚