SQL Server: Linked server select query into a new table

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s