BCP可以做到。还有很多前台具都有类似的功能,比如BDE,PB的数据管道。CREATE PROCEDURE dt_databasebackup @str_backuppath varchar(255),@str_databasename varchar(50),@str_tablename varchar(50) AS declare @str_sqlexecute varchar(255) if @str_tablename=‘‘or @str_tablename=null begin declare backuptable_cursor cursor for select sysobjects.name from sysobjects where sysobjects.type=‘U‘ open backuptable_cursor fetch next from backuptable_cursor into @str_tablename while @@fetch_status=0 begin select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ out ‘+ @str_backuppath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘ exec master..xp_cmdshell @str_sqlexecute fetch next from backuptable_cursor into @str_tablename end deallocate backuptable_cursor end else begin select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ out ‘+ @str_backuppath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘ exec master..xp_cmdshell @str_sqlexecute end --数据库数据备份存储过程 go CREATE PROCEDURE dt_databaserestore @str_restorepath varchar(255),@str_databasename varchar(50),@str_tablename varchar(50) AS declare @str_sqlexecute varchar(255) if @str_tablename=‘‘ or @str_tablename=null begin declare restoretable_cursor cursor for select sysobjects.name from sysobjects where sysobjects.type=‘U‘ open restoretable_cursor fetch next from restoretable_cursor into @str_tablename while @@fetch_status=0 begin select @str_sqlexecute=‘truncate table ‘+@str_tablename execute(@str_sqlexecute) select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ in ‘+ @str_restorepath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘ exec master..xp_cmdshell @str_sqlexecute fetch next from restoretable_cursor into @str_tablename end deallocate restoretable_cursor end else begin select @str_sqlexecute=‘truncate table ‘+@str_tablename execute(@str_sqlexecute) select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ in ‘+ @str_restorepath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘ end --数据库数据恢复存储过程 go
declare @str_sqlexecute varchar(255)
if @str_tablename=‘‘or @str_tablename=null
begin
declare backuptable_cursor cursor for
select sysobjects.name from sysobjects where sysobjects.type=‘U‘
open backuptable_cursor
fetch next from backuptable_cursor into @str_tablename
while @@fetch_status=0
begin
select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ out ‘+ @str_backuppath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘
exec master..xp_cmdshell @str_sqlexecute
fetch next from backuptable_cursor into @str_tablename
end
deallocate backuptable_cursor
end
else
begin
select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ out ‘+ @str_backuppath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘
exec master..xp_cmdshell @str_sqlexecute
end
--数据库数据备份存储过程
go
CREATE PROCEDURE dt_databaserestore @str_restorepath varchar(255),@str_databasename varchar(50),@str_tablename varchar(50) AS
declare @str_sqlexecute varchar(255)
if @str_tablename=‘‘ or @str_tablename=null
begin
declare restoretable_cursor cursor for
select sysobjects.name from sysobjects where sysobjects.type=‘U‘
open restoretable_cursor
fetch next from restoretable_cursor into @str_tablename
while @@fetch_status=0
begin
select @str_sqlexecute=‘truncate table ‘+@str_tablename
execute(@str_sqlexecute)
select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ in ‘+ @str_restorepath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘
exec master..xp_cmdshell @str_sqlexecute
fetch next from restoretable_cursor into @str_tablename
end
deallocate restoretable_cursor
end
else
begin
select @str_sqlexecute=‘truncate table ‘+@str_tablename
execute(@str_sqlexecute)
select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ in ‘+ @str_restorepath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘
end
--数据库数据恢复存储过程
go
比如:SQL的话就有很多方法:
1.可以利用export and import data工具来进行,各种选择(包括从那个表转移到哪个表,从哪个字段转移到哪个字段等)你可以设置,最好你用一下就知道了。
2.可以在SQL 的查询分析器中用程序来完成,利用游标,先从要转移的库表中选择数据赋埴给游标,在将游标的埴写入要转移到的数据库表的字段中就可以了。
3最后说一句,我试过,微软的东西很很多都可以数据互相转移,也就是导入导出操作。
不知道你明白了没有.
但如果可以考虑别的方法,我说的dblink方法,jimmyxing()说的方法,还有zqllyh的方法都可以。你可以综合考虑一下。
我给个建议:如果是实时运行的,就用程序。否则就用别的方法效率更高。
insert into database1.user1.table1
select * from database2.user2.table2