SQL Server - A script to loop through all remote tables and perform "Select * into ...'

Question

Here's what I'd like to do.

For each table in linkedserver.database whose tablename is like 'text%'

(inside loop)

A. If current_table exists locally, drop it

B. select * into table.name (local) from linkedserver.tablename (copy schema + data)

C. Possibly check for errors and Print some text about it?

Next

Any idea if this script is possible? I'm very clueless about working with table names if it would be possible to "select * into @Variable_Storing_Table_Name from LinkedServer.DB.@Variable_Storing_Table_Name"

Thanks for your time

Answer 1

There is an undocumented SQL Server function called sp_foreachtable that might do what you want. I'm not sure if it works on linked databases though... a Web search might turn something up.

Answer 2

Well, here's how to do this using a cursor:

use database
go

declare @link_table nvarchar(255)
declare @local_table nvarchar(255)

declare table_list cursor for
select
    tlink.name,
    tlocal.name
from
    linkedserver.database.sys.tables tlink
    left outer join sys.tables tlocal on
        tlink.name = tlocal.name

open table_list

fetch next from table_list into @link_table, @local_table

while @@FETCH_STATUS = 0
begin
    begin try
        if @local_table is not null
        begin
            sp_executesql N'drop table ' + quotename(@local_table)
        end

        sp_executesql N'select * into ' + quotename(@link_table) +
            ' from linkedserver.database..' + quotename(@link_table)
        print @link_table + ' copied.'
    end try
    begin catch    
        print 'Error: ' + ERROR_MESSAGE()
    end catch

    fetch next from table_list into @link_table, @local_table
end

close table_list
deallocate table_list

While cursors should generally be avoided, here you're looking to do a lot of logic behind each and every row. So, here it is. What it does is grab all of the linked tables and match any of the local tables to those, or null if the local table doesn't exist. This places it in a cursor, which we can use to iterate through the rowset.

The fetch next command grabs the next row from our cursor and then applies your logic to it (drop it if the local table exists, then do a select * into...).

You can catch errors one of two ways. I used the try...catch block, but you can also check @@ERROR and see if it's not equal to zero. Really, whatever you feel most comfortable with.

As a disclaimer for the anti-cursor crowd: Cursors aren't evil, they're just often used improperly.