- Use all user tables that store imported link servers
- This example links to the FoxPro database, SQL Server local instance PSMS
First, create four storage procedures
--1, establish a link server
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, read a table of the link server into a temporary table
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, establish a local table by calling a temporary table
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'susertable ') = 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, use a cursor guide all user tables on the link server
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
DEAALLOCATE CRTMPTABLE
Exec ('Drop Table ## TMPAllTables)
--In, execution
Exec [dbo]. [P_importAllTables] @ server = 'psms2', @ dbpath = 'c: / psms_db2'
