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