sql语句妙用,各种sql语句的详细用法与讲解(6)

as begin DECLARE @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,'INSERT INTO '+@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 b where a.name=@tablename1 and a.id=b.id and 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.colid from sysobjects a ,syscolumns b where a.name=@tablename2 and a.id=b.id and a.type='U' update #ins_tab set col_name2=b.name from sysobjects a ,syscolumns b,#ins_tab c where a.name=@tablename2 and a.id=b.id and a.type='U' and c.col_name1=b.name and c.fg=2 update #ins_tab set 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,'',colid FROM #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=2 select 0 AS FG,'-- INSERT '+@tablename1+' FROM '+@tablename2,0 AS colid union select fg,col_name1,colid from #ins_tab where fg=0 union select fg,col_name1,colid from #ins_tab where fg=1 union select fg,CASE WHEN colid=@MAX_ID THEN ' ' +col_name1 ELSE ' '+col_name1+',' END AS col_name1, colid from #ins_tab where fg=2 union select fg,col_name1,colid from #ins_tab where fg=3 union select fg,col_name1,colid from #ins_tab where fg=4 union select 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=2 union select 6 as fg,col_name1,colid from #ins_tab where fg=6 union select 6 AS FG,'WHERE NOT EXISTS(SELECT 1 FROM '+@tablename1+' WHERE '+@tablename1+'.='+@tablename2+'.',21 AS colid union select 8 AS FG,'-- UPDATE '+@tablename1+' FROM '+@tablename2,0 AS colid UNION select 7 as fg,col_name1,colid from #ins_tab where fg=5 --UPDATE union select 8 as fg,col_name1,colid from #ins_tab where fg=7 union select 9 as fg,col_name1,colid from #ins_tab where fg=8 union select 10 as fg,CASE WHEN colid=@MAX_ID THEN col_name1 ELSE col_name1+',' END AS col_name1, colid from #ins_tab where fg=9 union select 11 as fg,col_name1,colid from #ins_tab where fg=10 union select 12 as fg,col_name1,colid from #ins_tab where fg=11 union select 13 as fg,col_name1,colid from #ins_tab where fg=12 order by fg,colid drop table #ins_tab end go SET ANSI_NULLS OFF go SET QUOTED_IDENTIFIER OFF go IF OBJECT_ID('dbo.sp_showtable_insert') IS NOT NULL PRINT '<<< CREATED PROCEDURE dbo.sp_showtable_insert >>>' ELSE PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_showtable_insert >>>' go 实现跨多个表格的数据进行组合的SQL语句（ 1）