Share via

Is it possible to move data from "Linked Servers" (within "Server Objects") to "Databases" in SSMS

Anand Polamarasetti 101 Reputation points
2021-01-20T04:17:33.543+00:00

From SQL Server management studio, I created a linked server which is my AWS Athena awsdatacatalog. Now I am able to query my Athena external tables from SSMS.

Is it possible to move the data (of external tables) from "Linked Servers" to the actual "Databases"? If yes, please let me know the steps.

SQL Server Other
Accepted answer
  1. Anand Polamarasetti 101 Reputation points
    2021-01-20T06:02:41.59+00:00

    Yes, tried and was successful. Planning to make it a scheduled script like a nightly load. Thanks.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator
    2021-01-20T06:20:30.167+00:00

    You can create a stored procedure (having Truncate/Insert script) and use this as part of job step in SQL Server Agent job

    Schedule this job to run daily

    Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

    0 comments
  2. CathyJi-MSFT 22,396 Reputation points Microsoft External Staff
    2021-01-21T08:23:16.28+00:00

    Hi @Anand Polamarasetti ,

    We have not received a response from you. Did you try the below query offered by VaibhavChaudhari to move data to SQL DB? 

    Insert into CurrentDB.dbo.Table1  
Select <cols> from LinkedAWSServer.DB.dbo.Table1

    If the query worked, please do "Accept Answer". If it is not work, please let me know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

    Best regards,
    Cathy

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.