--利用储存导入链接服务器的所有用户表
--本例链接到FOXPRO数据库, SQL Server本地实例PSMS
--一、 创建四个储存过程
--1、建立链接服务器
CREATE PROCEDURE P_CreateSrv
@server nvarchar(30)='PSMS',
@DBPath nvarchar(30)='C:/PSMS_DB1'
AS
DECLARE @SourceDB nvarchar(400)
IF EXISTS(select * from master..sysservers where srvname=@server)
EXEC sp_dropserver @server, N'droplogins'
SELECT @SourceDB='Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB='+@DBPath+';'
EXEC sp_addlinkedserver
@server,
'',
'MSDASQL',
null,
NULL,
@SourceDB
--2、将链接服务器的一个表读入临时表
CREATE PROCEDURE P_Table
@table nvarchar(40)='editbase',
@server nvarchar(40)='psms'
AS
declare @Sql nvarchar(4000),@tmpTable nvarchar(30)
SELECT @tmpTable='##'+LTRIM(@table)
IF EXISTS (select * from tempdb.dbo.sysobjects where [name]=@tmpTable and xtype=N'U')
EXEC ('drop table '+@tmpTable)
select @Sql='SELECT * into '+@tmpTable+' FROM OPENQUERY('+@server+', ''select * from '+@table+''')'
exec (@Sql)
--3、通过调用临时表建立本地表
CREATE PROCEDURE P_ImportTable
@table nvarchar(40)='editbase',
@server nvarchar(40)='psms'
AS
declare @Sql nvarchar(4000),@tmpTable nvarchar(30)
IF EXISTS (select * from dbo.sysobjects where id = object_id(@table) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC ('drop table '+@table)
EXEC dbo.P_Table @table,@server
SELECT @tmpTable='##'+LTRIM(@table)
SELECT @Sql='SELECT * into '+@Table+' FROM '+@tmpTable
EXEC (@Sql)
EXEC ('drop table '+@tmpTable)
--4、运用游标导入链接服务器上的所有用户表
CREATE PROCEDURE P_ImportAllTables
@server nvarchar(30)='psms',
@DBPath nvarchar(30)='c:/psms_db1'
AS
EXEC [dbo].[P_CreateSrv] @server, @DBPath
DECLARE @Sql nvarchar(4000)
SELECT @Sql='select * into ##tmpAllTables from openrowset(''MSDASQL'',''DRIVER={SQL Server};Server=(Local);Initial Catalog=psms;Integrated Security=SSPI;'',''set fmtonly off exec sp_tables_ex '+@server+''')'
EXEC(@Sql)
DECLARE @tmpTable nvarchar(30)
DECLARE crtmpTable CURSOR FOR
SELECT TABLE_NAME FROM ##tmpAllTables WHERE TABLE_TYPE=N'TABLE'
OPEN crtmpTable
FETCH NEXT FROM crtmpTable INTO @tmpTable
WHILE(@@fetch_status<>-1)
BEGIN
IF (@@fetch_status<>-2)
BEGIN
EXEC [dbo].[P_ImportTable] @tmpTable, @server
END
FETCH NEXT FROM crtmpTable
INTO @tmpTable
END
CLOSE crtmpTable
DEALLOCATE crtmpTable
EXEC ('drop table ##tmpAllTables')
--二、 执行
EXEC [dbo].[P_ImportAllTables] @server='Psms2', @DBPath='C:/psms_db2'