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 |