因为工作中经常需要将数据从一个数据库导入到另一个数据库中，所以将这个功能写成一个存储过程，以方便调用。现在粘贴出来供大家参考：
1,以下示例中用到了syscolumns,sysobjects等系统视图，在sql2005及以后的版本中，大家也可以使用sys.columns与sys.objects来代替，只要修改对应的字段即可。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create PROCEDURE [dbo].[Usp_Sys_ImportData] @SourceDBName varchar(128),@TargetDBName varchar(128),@TableName varchar(128) AS
/*
存储过程名称:Usp_Sys_ImportData
功能简述：从来源数据库导入指定表数据到目标数据库
相关对象：
参数：@SourceDBName varchar(128) 来源数据库名称
@TargetDBName varchar(128) 目标数据库名称
@TableName varchar(128) 表名
*/
SET NOCOUNT ON;
declare @TempSql varchar(max)
--取来源表栏位(不包括
计算列)
declare @T_SourceColumn Table(ColumnID int not null,ColumnName varchar(128) not null)
select @TempSql = 'select t1.colid,t1.name'
+char(10)+'from ' + @SourceDBName + '..syscolumns t1'
+char(10)+'join ' + @SourceDBName + '..sysobjects t2 on t2.id = t1.id'
+char(10)+'where t2.name = ''' + @TableName + ''''
+char(10)+' and t1.iscomputed = 0'
+char(10)+'order by t1.colid'
insert into @T_SourceColumn(ColumnID,ColumnName)
exec(@TempSql)
--取目标表栏位(不包括计算列)
declare @T_TargetColumn Table(ColumnID int not null,ColumnName varchar(128) not null)
select @TempSql = 'select t1.colid,t1.name'
+char(10)+'from ' + @TargetDBName + '..syscolumns t1'
+char(10)+'join ' + @TargetDBName + '..sysobjects t2 on t2.id = t1.id'
+char(10)+'where t2.name = ''' + @TableName + ''''
+char(10)+' and t1.iscomputed = 0'
+char(10)+'order by t1.colid'
insert into @T_TargetColumn(ColumnID,ColumnName)
exec(@TempSql)
--检查是否有自增列
declare @T_TargetIdentityColumn Table(ColumnID int not null,ColumnName varchar(128) not null)
select @TempSql = 'select t1.colid,t1.name'
+char(10)+'from ' + @TargetDBName + '..syscolumns t1'
+char(10)+'join ' + @TargetDBName + '..sysobjects t2 on t2.id = t1.id'
+char(10)+'where t2.name = ''' + @TableName + ''''
+char(10)+' and t1.colstat = 1'
+char(10)+'order by t1.colid'
insert into @T_TargetIdentityColumn(ColumnID,ColumnName)
exec(@TempSql)
declare @HasIdentityColumn int
if exists(select * from @T_TargetIdentityColumn)
begin
select @HasIdentityColumn = 1
end
else
begin
select @HasIdentityColumn = 0
end
--取要导入的栏位(共同的栏位)
declare @ColumnList varchar(max)
select @ColumnList = ''
select @ColumnList = @ColumnList + ',' + t1.ColumnName
from @T_SourceColumn t1,
@T_TargetColumn t2
where t1.ColumnName = t2.ColumnName
order by t1.ColumnID
if left(@ColumnList,1) = ','
begin
select @ColumnList =
substring(@ColumnList,2,len(@ColumnList) - 1)
end
if @ColumnList < > ''
begin
--构造导入数据的SQL
declare @ExecSql varchar(max)
select @ExecSql = 'alter table ' + @TargetDBName + '..' + @TableName + ' disable trigger all'
+ char(10) + 'delete from ' + @TargetDBName + '..' + @TableName
if @HasIdentityColumn = 1
begin
select @ExecSql = @ExecSql + char(10) + 'set identity_insert ' + @TargetDBName + '..' + @TableName + ' on'
end
select @ExecSql = @ExecSql + char(10) + 'insert into ' + @TargetDBName + '..' + @TableName + '(' + @ColumnList + ')'
+ char(10) + 'select ' + @ColumnList + ' from ' + @SourceDBName + '..' + @TableName
if @HasIdentityColumn = 1
begin
select @ExecSql = @ExecSql + char(10) + 'set identity_insert ' + @TargetDBName + '..' + @TableName + ' off'
end
select @ExecSql = @ExecSql + char(10) + 'alter table ' + @TargetDBName + '..' + @TableName + ' enable trigger all'
print @ExecSql
--执行导入
exec(@ExecSql)
if @@error = 0
begin
return 1
end
else
begin
return -1
end
end
else
begin
return 1
end