...
Paste code macro | ||||
---|---|---|---|---|
| ||||
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; |
...