两个表间,不存在的insert,存在地update
IF OBJECT_ID('dbo.sp_showtable_insert') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_showtable_insert
IF OBJECT_ID('dbo.sp_showtable_insert') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_showtable_insert >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_showtable_insert >>>'
END
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
create procedure [dbo].[sp_showtable_insert] @tablename1 varchar(100), @tablename2 varchar(100)
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