我们经常需要将一个表insert到另外一个表中,而大部分的字段名称都一些,这样,我们就可以使用一下的sp来实现,减少很多的工作量如将t2 insert到 t1

run:sp_mapping 't1','t2'

IF OBJECT_ID('dbo.sp_mapping') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_mapping
IF OBJECT_ID('dbo.sp_mapping') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_mapping >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_mapping >>>'
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
create proc sp_mapping (@tablename1 nvarchar(50),@tablename2 nvarchar(50))
as
begin
create table #map
(
table_nm1 nvarchar(100),
column_nm1 nvarchar(100),
type_1 nvarchar(20),
null_1 nvarchar(10),
colid int,
table_nm2 nvarchar(100),
column_nm2 nvarchar(100),
type_2 nvarchar(20),
null_2 nvarchar(10)
)

create table #map2
(
table_nm1 nvarchar(100),
column_nm1 nvarchar(100),
type_1 nvarchar(20),
null_1 nvarchar(10),
)

insert #map(table_nm1,column_nm1,type_1,null_1,colid,table_nm2,column_nm2,type_2 ,null_2)
select @tablename1,b.name as ColumnName, case when c.name in ( 'nvarchar','char','nchar','varchar') then c.name+'('+convert(varchar(4),b.prec)+')'
when c.name in ('decimal','numeric','float')then c.name+'('+convert(varchar(4),b.prec)+','+convert(varchar(4),b.scale)+')'
when c.name in ('text','tinyint','image','int','smalldatetime','datetime','bigint','timestamp','money') then c.name
else '?????????'
end as Type,
case b.isnullable when 0 then 'not null' else 'null' end as 'Null' ,b.colid,'','','',''
from sysobjects a ,syscolumns b, systypes c
where a.name=@tablename1
and a.id=b.id
and b.usertype=c.usertype
and b.xusertype=c.xusertype
order by b.colorder

insert #map2(table_nm1,column_nm1,type_1,null_1)
select @tablename2,b.name as ColumnName, case when c.name in ( 'nvarchar','char','nchar','varchar') then c.name+'('+convert(varchar(4),b.prec)+')'
when c.name in ('decimal','numeric','float')then c.name+'('+convert(varchar(4),b.prec)+','+convert(varchar(4),b.scale)+')'
when c.name in ('text','tinyint','image','int','smalldatetime','datetime','bigint','timestamp','money') then c.name
else '?????????'
end as Type,
case b.isnullable when 0 then 'not null' else 'null' end as 'Null'
from sysobjects a ,syscolumns b, systypes c
where a.name=@tablename2
and a.id=b.id
and b.usertype=c.usertype
and b.xusertype=c.xusertype
order by b.colorder

update #map
set #map.table_nm2=#map2.table_nm1,
#map.column_nm2=#map2.column_nm1,
#map.type_2= #map2.type_1,
#map.null_2 =#map.null_1
from #map,#map2
where #map.column_nm1=#map2.column_nm1

select colid as "#",table_nm1 as INLayer ,column_nm1 as ColumnName1,type_1 ,null_1,table_nm2 as MainLayer,column_nm2 as ColumnName1,type_2 ,null_2 from #map order by colid

end
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.sp_mapping') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_mapping >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_mapping >>>'
go