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

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

 

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