利用储存导入链接服务器的所有用户表

--本例链接到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'