积极答复者
求问存储过程问题..

问题
答案
-
假设有2个表,tb_AA,tb_BB,怎么写存储过程才能做到从第2个表中选取出第1个表所含有的字段然后插入第一个表呢..主键是tb_BB中的ID,tb_AA也是ID
use Tempdb go create table tb_AA(ID int Primary key,Code nvarchar(20),Num int) create table tb_BB(ID int Primary key,Code nvarchar(20),Num int,Num2 int) create procedure P_TableCopy ( @Tab1 sysname, --tb_AA @Tab2 sysname --tb_BB ) as begin declare @s nvarchar(2000) set @s='' select @s=@s+','+a.Name from syscolumns a,syscolumns b where a.ID=object_id(@Tab1) and b.ID=object_id(@Tab2) and a.Name=b.Name set @s='insert '+@Tab1+'('+stuff(@s,1,1,'')+') select '+stuff(@s,1,1,'')+' from '+@Tab2+' as a where not exists(select 1 from '+@Tab1+' where ID=a.ID)' exec(@s) end go 生成的語句格式為 insert tb_AA(ID,Code,Num) select ID,Code,Num from tb_BB as a where not exists(select 1 from tb_AA where ID=a.ID)
ROY WU(吳熹)
全部回复
-
假设有2个表,tb_AA,tb_BB,怎么写存储过程才能做到从第2个表中选取出第1个表所含有的字段然后插入第一个表呢..主键是tb_BB中的ID,tb_AA也是ID
use Tempdb go create table tb_AA(ID int Primary key,Code nvarchar(20),Num int) create table tb_BB(ID int Primary key,Code nvarchar(20),Num int,Num2 int) create procedure P_TableCopy ( @Tab1 sysname, --tb_AA @Tab2 sysname --tb_BB ) as begin declare @s nvarchar(2000) set @s='' select @s=@s+','+a.Name from syscolumns a,syscolumns b where a.ID=object_id(@Tab1) and b.ID=object_id(@Tab2) and a.Name=b.Name set @s='insert '+@Tab1+'('+stuff(@s,1,1,'')+') select '+stuff(@s,1,1,'')+' from '+@Tab2+' as a where not exists(select 1 from '+@Tab1+' where ID=a.ID)' exec(@s) end go 生成的語句格式為 insert tb_AA(ID,Code,Num) select ID,Code,Num from tb_BB as a where not exists(select 1 from tb_AA where ID=a.ID)
ROY WU(吳熹)