Copy all views from Linked Server into new database

Question

In SQL Server Management Studio, I have a Server Object-> Linked Server where there are cube views. I am trying to make a copy of all of these views and save them on a separate SQL database on same server so that I can connect to these new views using things like PowerBI and Power Query. Any suggestions as to how I would go about doing this? I know that I could write individual queries such as:

Select * INTO [new_table] from [linked_server_view].[Table]

However there are hundreds of views in the Linked Server and this would be very time consuming. Thanks,

Can you use Visual Studio with an SSDT project via Schema Compare? — Jacob H, Sep 17 '18 at 15:46

score 1 · Accepted Answer · answered Sep 17 '18 at 16:12

One possible solution using your initial idea of writing the individual queries would be to get SQL to write the queries for you. Run something like

SELECT 'select * into ' + name + ' FROM linkedserver.database.schema.' + name
FROM linkedserver.database.sys.views

Copy the results, paste into editor, and run.

You can modify the query as needed. I was just showing the basic idea of using SQL to produce the query for you.

score 0 · Answer 2 · answered Sep 17 '18 at 16:15

0

SELECT
    'select ' + '* into ' + TABLE_NAME +  ' from ' + TABLE_CATALOG + '.' + TABLE_NAME

FROM
    INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'View'

answered Sep 17 '18 at 16:15

LocN

41
4

Copy all views from Linked Server into new database

2 Answers2