1:从源数据库中生成sql脚本
2:新建一个目标数据库,执行源数据库sql脚本来创建结构一样的数据库
3:执行sql语句输出导入数据sql语句
4:在目标数据库中执行导入数据sql语句
在源数据库执行
CREATE FUNCTION [dbo].[S_GetColumns]
(
@Table nvarchar(200)
)
RETURNS nvarchar(4000)
AS
BEGIN
declare @sRs nvarchar(4000)
set @sRs= (select name+',' from sys.all_columns
where object_id=(select object_id from sys.all_objects where type='u' and name=@Table) for xml path('')
)
set @sRs=SUBSTRING(@sRs,1,LEN(@sRs)-1)
return @sRs
END
在源数据库执行
CREATE FUNCTION [dbo].[S_GetIdentity]
(
@Table nvarchar(200)
)
RETURNS nvarchar(4000)
AS
BEGIN
declare @sRs nvarchar(4000)
set @sRs= isnull((select name+',' from sys.all_columns
where is_identity=1 and object_id=(select object_id from sys.all_objects where type='u' and name=@Table) for xml path('')
),'')
if LEN(@sRs)>0
begin
set @sRs=SUBSTRING(@sRs,1,LEN(@sRs)-1)
end
return @sRs
END
GO
在源数据库执行,拷贝结果内容在目标数据库执行
declare @sql nvarchar(4000)
declare @src nvarchar(100)
declare @table nvarchar(200)
declare @cols nvarchar(4000)
--这个指定原数据库的拥有关系,必须在目标数据库的查询分析器中执行
set @src='mt.dbo.'
declare Cur112 cursor for
select [name] from [sysobjects] where [type] = 'u' and [name] not in('dtproperties','sysdiagrams') order by [name]
OPEN Cur112
FETCH NEXT FROM Cur112 into @table
WHILE @@FETCH_STATUS = 0
begin
set @sql=''
if(len(dbo.S_GetIdentity(@table))>0)--存在自动编号
begin
set @cols=dbo.S_GetColumns(@table)
set @sql=@sql+'set IDENTITY_INSERT '+@table+' on;'
set @sql=@sql+'insert into '+@table+'('+@cols+') select '+@cols+' from '+@src+@table+';'
set @sql=@sql+'set IDENTITY_INSERT '+@table+' off;'
end else
begin
set @sql=@sql+'insert into '+@table+' select * from '+@src+@table+';'
end
print @sql
FETCH NEXT FROM Cur112 into @table
END
CLOSE Cur112
DEALLOCATE Cur112