SQL SELECT INTO Examples and Linked Server in SQL Server 2008

SQL SELECT INTO Examples

Copy table from one database to another

Create a linked server to the source server. The easiest way is to right click "Linked Servers" in Management Studio; it's under Management -> Server Objects.

Then you can copy the table using a 4-part name, server.database.schema.table:

SELECT * INTO DBNAME.DBO.NEWTABLE FROM LINKEDSERVER.DBNAME.DBO.OLDTABLE

This will both create the new table with the same structure as the original one and copy the data over.

How to Create a Linked Server?

To create a linked server (SQL Server 2008)

1. Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.
2. In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect.
3. In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.
4. In the New Linked Server dialog box, on the General page, in the Linked server text box, enter the full network name of the SQL Server you want to link to.
5. Under Server type, click SQL Server.
6. In the left pane of the New Linked Server dialog, under Select a page, choose Security.
7. You will need to map a local server login to a remote server login. On the right side of the Security page, click the Add button.
8. Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.
9. In the left pane of the New Linked Server dialog, under Select a page, choose Server Options. Set the RPC and RPC Out parameters to True, and then click OK.

Note

This procedure often refers to the server you are linking to as the remote server. This is for convenience only, to indicate the relationship of the linked (“remote”) server to the local server. Do not confuse this usage with the obsolete remote server functionality in SQL Server.

Copying data within a same server TheDestinationDB is theh current database.

USE TheDestinationDB;

SELECT * INTO NewTable FROM TheSourceDB.Schemaname.OldTable

Create a backup copy

Another Option with a single db server: SELECT * INTO NewTable FROM OldTable

Copy only a few columns into the new table:

SELECT CustomerName, ContactName INTO CustomersBackup2015 FROM Customers;

Copy data from more than one table into the new table:

SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrderBackup2015

FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID;

Tip: The SELECT INTO statement can also be used to create a new, empty table using the schema of another. Just Add a WHERE clause that causes the query to return no data:

SELECT * INTO CustomerNew FROM dbo.Customer WHERE 1=0;

Copy only the 'India' customers into the new table:

SELECT * INTO tblIndiaCustomersBackup2015 FROM Customers WHERE Country='India';