Read More »
SQL SELECT INTO Examples
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?
- Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.
- In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect.
- In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.
- 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.
- Under Server type, click SQL Server.
- In the left pane of the New Linked Server dialog, under Select a page, choose Security.
- 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.
- 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.
- 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.
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.
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';