科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网网络频道两个表间不存在的insert与存在的update

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

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

本文将为大家介绍两个表间不存在的insert与存在的update。

作者:赛迪网 Alice 来源:天新网 2008年3月22日

关键字: 数据库 SQL Server SQL Mssql

  • 评论
  • 分享微博
  • 分享邮件

两个表间,不存在的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
    • 评论
    • 分享微博
    • 分享邮件
    邮件订阅

    如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

    重磅专题
    往期文章
    最新文章