幾次遇到關於兩個無關的表合併問題，因人太懶了，我都不知道如解決的了，
最近又遇到了(原因是同類產品有不同的數據結構表---想想那個設計者我就想日)，沒法必須要解決了，終於下定決心了來解決了
如下：
/*********************************************************************************************************************
表一
A B C D FED1 FED2 FED3
147 110 141.06 147.88 NULL 1998-12-01 00:00:00.000 122.15
表二
E F G H I J K FED1 FED2 FED3
146.81 114.38 182.88 143.06 152.31 47.67 63.36 NULL 1999-01-01 00:00:00.000 126.69
合並表：
A B C D E F G H I J K FED1 FED2 FED3
147 110 141.06 147.88 0 0 0 0 0 0 0 NULL 1998-12-01 00:00:00.000 122.15
0 0 0 0 146.81 114.38 182.88 143.06 152.31 47.67 63.36 NULL 1999-01-01 00:00:00.000 126.69
*@Function:從表一表二得到合併表(注：表一表二的結構未知)
*@Author: 何利民
*@Create Date:2005-02-24
*@Parameters:@tbName1表一@tbName2表二
*********************************************************************************************************************/
CREATE PROCEDURE [dbo].[MergeTable]
(
@tbName1 nvarchar(50),
@tbName2 nvarchar(50)
)
AS
--將要合併的兩個表分並存在臨時表##tmp與##tmp1
declare @sqlStr nvarchar(4000)
set @sqlStr='select * into ##tmp from '+@tbName1+'; select * into ##tmp1 from '+@tbName2
exec(@sqlStr)
declare @tb1 nvarchar(50)
declare @tb2 nvarchar(50)
set @tb1='##tmp'
set @tb2='##tmp1'
--在表一中增加表二中多余的結構
set @sqlStr=''
declare @fed nvarchar(50)
Declare newcursor Cursor local forward_only static read_only type_warning
for select [name] from tempdb.dbo.syscolumns where id=(select id from tempdb.dbo.sysobjects where id=object_id(N'[tempdb].[dbo].['+@tb2+']'))
open newcursor
fetch next from newcursor into @fed
while(@@fetch_status=0)
begin
if(not exists(select [name] from tempdb.dbo.syscolumns where id=(select id from tempdb.dbo.sysobjects where id=object_id(N'[tempdb].[dbo].['+@tb1+']')) and name=@fed))
begin
set @sqlStr=@sqlStr+'Alter table '+@tb1+' '
set @sqlStr=@sqlStr+'add ['+@fed+'] nvarchar(10);'
end
fetch next from newcursor into @fed
end
exec(@sqlStr)
close newcursor
deallocate newcursor
--在表二中增加表一中多余的結構
set @sqlStr=''
Declare newcursor Cursor local forward_only static read_only type_warning
for select [name] from tempdb.dbo.syscolumns where id=(select id from tempdb.dbo.sysobjects where id=object_id(N'[tempdb].[dbo].['+@tb1+']'))
open newcursor
fetch next from newcursor into @fed
while(@@fetch_status=0)
begin
if(not exists(select [name] from tempdb.dbo.syscolumns where id=(select id from tempdb.dbo.sysobjects where id=object_id(N'[tempdb].[dbo].['+@tb2+']')) and name=@fed))
begin
set @sqlStr=@sqlStr+'Alter table '+@tb2+' '
set @sqlStr=@sqlStr+'add ['+@fed+'] nvarchar(10);'
end
fetch next from newcursor into @fed
end
exec(@sqlStr)
close newcursor
--對兩表進行合併結果存入臨時表##resultTable
declare @fedStr nvarchar(4000)
set @fedStr=''
select @fedStr=@fedStr+'['+name+'],' from tempdb.dbo.syscolumns where id=(select id from tempdb.dbo.sysobjects where id=object_id(N'[tempdb].[dbo].['+@tb1+']'))
if(right(@fedStr,1)=',')
set @fedStr=left(@fedStr,len(@fedStr)-1)
set @sqlStr='select * into ##resultTable from (select '+@fedStr+' from ##tmp union all select '+@fedStr+' from ##tmp1) a'
exec(@sqlStr)
drop table ##tmp
drop table ##tmp1
GO
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。