Page Comparison

Create Procedure sp_Linked_Server_Import @linked_server_name NVarchar(100), @table_schema NVarchar(100) As

-- Declarations

-- Nb, the sp_ExecuteSQL function can only accept unicode string data so all string data is
-- stored as nVarchar

-- System Defined
Declare @Rows Int
Declare @RowCounter Int
Declare @tName nVarchar(200)
Declare @DropStatement nVarchar(400)
Declare @sQuery nVarchar(800)
Declare @DataPull nVarchar(400)
Declare @FinalQuery nVarchar(400)
Declare @LServer nVarchar(100)
Declare @tSchema nVarchar(100)

-- Defined on execution or, if executed as a stand-alone, alter these
Set @LServer = @linked_server_name
Set @tSchema = @table_schema

-- For this kind of work, it can be useful to persist the working table, maybe you want to check 
-- that all tables were imported, and identify those that did not
-- Part 1a Drop and recreate the table list
If Object_ID('TS_Table_List') Is Not Null Drop Table TS_Table_List;

-- Now re-create

Create Table TS_Table_List(
	TS_Table_List_ID Int Identity(1,1) Not Null
	,Table_Catalogue Varchar(200) Null
	,Table_Schema Varchar(200) Null
	,Table_Name Varchar(200) Null
	,Table_Type Varchar(200) Null
	,Remarks Varchar(500) Null
);


-- Part 1b, fetch list of tables from Linked Server
-- @table_server = linked server name
-- @table_schema = schema
-- @table_type = 'Table' or 'View'

Insert Into TS_Table_List
EXEC sp_tables_ex @table_server = @LServer, @table_schema = @tSchema ;


-- Part 2: Loop through the list selecting the Table Name as the variable
-- and using this to control the res of the script

Set @RowCounter = 1
Select @Rows = Count(1)+1 From TS_Table_List

While @RowCounter < @Rows
	Begin
	-- Define your variables here (they need to be reset)
		Set @DropStatement = 'If Object_ID(''TableName'') Is Not Null Drop Table TableName' -- This is an import rather than append, so drops a pre-existing table of the same name
		Set @sQuery = 'Select * From TableName'
		Set @DataPull = 'Select * Into z_stg_table From Openquery(@LServer,''@sQuery'')'
		Set @FinalQuery = 'Select * Into NewTableName From z_stg_table'

		If Object_ID('z_stg_table') Is Not Null Drop Table z_stg_table
		
	-- Begin the work
		Select @tname = Table_Name From TS_Table_List
		Where TS_Table_List_ID = @RowCounter
	
		-- Drop the table if it exists
		Set @DropStatement = Replace(@DropStatement,'TableName',@tName)
	
		If Object_ID(@tName) Is Not Null Exec sp_ExecuteSQL @DropStatement
	
		-- Assign the table to the query
		Set @sQuery = Replace(@sQuery,'TableName',@tname)

		-- Now Import the table to a temp_table	
	
		Set @DataPull = Replace(@DataPull, '@LServer',@LServer)
		Set @DataPull = Replace(@DataPull, '@sQuery',@sQuery)

		Begin Try

		Exec sp_ExecuteSQL @DataPull
		
		End Try
		Begin Catch
		End Catch
		-- Move the data from the temp table into its actual table

		Select @FinalQuery = Replace(@FinalQuery,'NewTableName',@tname)

		Exec sp_ExecuteSQL @FinalQuery
		
		-- tidy-up
		If Object_ID('z_stg_table') Is Not Null Drop Table z_stg_table
		Select @RowCounter = @RowCounter + 1
	End;

-- Part 3: Final check to identify the tables that could not be imported

Select TS_Tables As Missing_Tables
From (

	Select
	a.Table_Name As TS_Tables
	,b.Table_Name As Imported_Tables
	From TS_Table_List a
	Left Join (
		Select 
		[name] As Table_Name
		From sys.tables
		Where [type_desc] = 'USER_TABLE'
		And [name] Not In('TS_Table_List') -- prepared here for you to list the tables you don't want to include in the output

	)b On a.Table_Name = b.Table_Name
)a1
Where Imported_Tables Is Null;