MS SQL – Copying a Linked Server Table

I recently used the following TSQL on a Microsoft SQL server to create and populate a table  from a table on a linked servers. In my case my linked server connection was linking to a MySQL database but the same will work for any Linked Server connection.

I am going to assume you already have you linked server connection setup. If you would like a guide on how to set one up just comment below.

I have this TSQL running nightly in a stored procedure. It may not be the most efficient method of achieving this but it does have the benefit that the whole MS SQL table is dropped and re-created each time the script runs. So if there are any schema changes at the source, those come across to MS SQL each night without any changes to the script.

The destination table is only dropped if the source linked table was successfully queried and has rows. Important in my case as I have reports reading from the MS SQL table that I didn’t want to completely break if the source failed to query. The report would just be a day out of date.

You will need to find and replace these placeholders in the script

SOURCE-LINKED-SERVER – The name of the linked server connection

SOURCE-DATABASE – The name of the source database

SOURCE-TABLE – The name of the table on the source linked server

DESTINATION-DATABASE – The name of the destination database. Needs to already exist

DESTINATION-SCHEMA – dbo unless it is something different in your case

DESTINATION-TABLE – The name of the destination name. Does not need to already exist

The destination database does need to already exists but the destination table does not have to already exist.

-- ================================
-- Copy SOURCE-TABLE
-- ================================
 
-- Drop the Temp table first if it already exists
IF OBJECT_ID(N'tempdb..#TEMP_SOURCE-TABLE') IS NOT NULL
       BEGIN
              DROP TABLE #TEMP_SOURCE-TABLE
       END
 
 
-- Insert the linked server table into a temp table
SELECT *
INTO #TEMP_SOURCE-TABLE
FROM OPENQUERY([SOURCE-LINKED-SERVER], N'SELECT * FROM SOURCE-DATABASE.SOURCE-TABLE')
 
-- If the temp table has rows drop the destination table and recreate the temp table
IF((SELECT count(*) FROM #TEMP_SOURCE-TABLE)>0)
BEGIN
       -- We drop in case the schema has changed at the source
       DROP TABLE IF EXISTS [DESTINATION-DATABASE].[DESTINATION-SCHEMA].[DESTINATION-TABLE]; 
       
       -- Recreate the destination table schema and data
       SELECT *
       INTO [DESTINATION-DATABASE].[DESTINATION-SCHEMA].[DESTINATION-TABLE]
       FROM #TEMP_SOURCE-TABLE
END