It’s always worth remembering SELECT INTO a new table or a temporary table, here’s and example:
SELECT [COL1], [COL2] INTO [DB name].[Schema name].[Table name] FROM [Source DB name].[Source Schema name].[Source Table name]
This will create the new table with the columns matching the name and types of the selected columns.
Alternatively you could select into a temporary table like so:
SELECT [COL1], [COL2] INTO #temporarytable FROM [Source DB name].[Source Schema name].[Source Table name]
For performance reasons you may want to copy the contents of a linked server into new or temporary tables in SQL Server, then run complex scripts and SQL against your SQL instance. Here’s an example of how I would do this, I am using OPENQUERY because that takes care of type inconsistencies for me:
SELECT [COL1], [COL2] INTO [DB name].[Schema name].[Table name] FROM OPENQUERY([Linked Server], N'SELECT * FROM SourceSchema.SourceTable')
Of course here your linked server could be anything, but I’ve used this for Oracle most recently and it was so much better than trying to export the data from Oracle and import it into SQL Server.