中甲

两个表间不存在的insert与存在的update_a

2020-01-17 00:59:11来源:励志吧0次阅读

两个表间,不存在的insert与存在的update示例:

IF OBJECT_ID('dbo.sp_showtable_insert') IS NOT NULLBEGINDROP PROCEDURE dbo.sp_showtable_insertIF OBJECT_ID('dbo.sp_showtable_insert') IS NOT NULLPRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_showtable_insert >>>'ELSEPRINT '<<< DROPPED PROCEDURE dbo.sp_showtable_insert >>>'ENDgoSET ANSI_NULLS ONgoSET QUOTED_IDENTIFIER ONgocreate procedure [dbo].[sp_showtable_insert] @tablename1 varchar(100), @tablename2 varchar(100)as beginDECLARE @MAX_ID NUMERIC(18,0)DECLARE @MAX_ID2 NUMERIC(18,0)create table #ins_tab(fg int,col_name1 nvarchar(150),col_name_val nvarchar(150),col_name2 nvarchar(150),colid numeric(18,0))insert into #ins_tab(fg,col_name1,col_name_val,colid) values(0,'INSERTINTO '+@tablename1,'',10)insert into #ins_tab(fg,col_name1,col_name_val) values(1,'(','')insert into #ins_tab(fg,col_name1,col_name_val,colid)select 2,b.name,'/*'+b.name+'_Value*/',b.colid from sysobjects a ,syscolumns bwhere a.name=@tablename1 and a.id=b.idand a.type='U'insert into #ins_tab(fg,col_name1,col_name_val) values(3,')','')insert into #ins_tab(fg,col_name1,col_name_val) values(4,'SELECT ','')insert into #ins_tab(fg,col_name1,col_name_val,colid)select 5,'--'+b.name,b.name,b.colidfrom sysobjects a ,syscolumns bwhere a.name=@tablename2 and a.id=b.idand a.type='U'update #ins_tabset col_name2=b.name from sysobjects a ,syscolumns b,#ins_tab cwhere a.name=@tablename2 and a.id=b.idand a.type='U' and c.col_name1=b.nameand c.fg=2update #ins_tabset col_name_val= CASE when isnull(col_name2,'1')='1' THEN 'null'+col_name_val else col_name2+col_name_val end where fg=2 delete #ins_tab from #ins_tab a where a.fg=5 and exists(select 1 from #ins_tab b where b.col_name1=a.col_name_val and b.fg=2)insert into #ins_tab(fg,col_name1,col_name_val,colid) values(6,'FROM '+@tablename2,'',10)insert into #ins_tab(fg,col_name1,col_name_val,colid) values(7,'UPDATE '+@tablename1,'',10)insert into #ins_tab(fg,col_name1,col_name_val) values(8,' SET ','')insert into #ins_tab(fg,col_name1,col_name_val,colid) SELECT 9,' '+substring(@tablename1+'.'+col_name1+' ',1,60)+'='+@tablename2+'.'+col_name1,'',colidFROM #ins_tab where fg=2 AND isnull(col_name2,'1')<>'1'insert into #ins_tab(fg,col_name1,col_name_val) SELECT 10,' FROM '+@tablename1+','+@tablename2,''insert into #ins_tab(fg,col_name1,col_name_val) SELECT 11,'WHERE '+@tablename1+'.='+@tablename2+'.',''SELECT @MAX_ID=MAX(colid) from #ins_tab where fg=2select 0 AS FG,'-- INSERT '+@tablename1+' FROM '+@tablename2,0 AS colidunionselect fg,col_name1,colid from #ins_tab where fg=0unionselect fg,col_name1,colid from #ins_tab where fg=1unionselect fg,CASE WHEN colid=@MAX_ID THEN ' '+col_name1 ELSE ' '+col_name1+',' END AS col_name1,colid from #ins_tab where fg=2unionselect fg,col_name1,colid from #ins_tab where fg=3unionselect fg,col_name1,colid from #ins_tab where fg=4unionselect 5 as fg,CASE WHEN colid=@MAX_ID THEN ' '+col_name_val ELSE ' '+col_name_val+',' END AS col_name1,colid from #ins_tab where fg=2unionselect 6 as fg,col_name1,colid from #ins_tab where fg=6unionselect 6 AS FG,'WHERE NOT EXISTS(SELECT 1 FROM '+@tablename1+'WHERE '+@tablename1+'.='+@tablename2+'.',21 AS colidunionselect 8 AS FG,'-- UPDATE '+@tablename1+' FROM '+@tablename2,0 AS colidUNIONselect 7 as fg,col_name1,colid from #ins_tab where fg=5--UPDATEunionselect 8 as fg,col_name1,colid from #ins_tab where fg=7unionselect 9 as fg,col_name1,colid from #ins_tab where fg=8unionselect 10 as fg,CASE WHEN colid=@MAX_ID THEN col_name1 ELSE col_name1+',' END AS col_name1,colid from #ins_tab where fg=9unionselect 11 as fg,col_name1,colid from #ins_tab where fg=10unionselect 12 as fg,col_name1,colid from #ins_tab where fg=11unionselect 13 as fg,col_name1,colid from #ins_tab where fg=12order by fg,coliddrop table #ins_tabendgoSET ANSI_NULLS OFFgoSET QUOTED_IDENTIFIER OFFgoIF OBJECT_ID('dbo.sp_showtable_insert') IS NOT NULLPRINT '<<< CREATED PROCEDURE dbo.sp_showtable_insert >>>'ELSEPRINT '<<< FAILED CREATING PROCEDURE dbo.sp_showtable_insert >>>'go

什么草药舒筋活络
舒尔佳奥利司他有用吗
小儿流行性感冒吃什么药
薏芽健脾凝胶作用
分享到: