Thursday, January 30

Select Into

SELECT INTO statement copies data and its structure from one table and inserts it into a new table.The new table will be created with the column-names and data types as defined in the SELECT statement. New column names can be given using the AS clause in select statement.

Syntax:
SELECT *
INTO table_new 
FROM table_exists;
Or 
SELECT column_name(s)         //copy only the columns specified  
INTO table_new                  
by us to the new table
FROM table_exists;


This can come in handy when you want to duplicate a table,taking backup for a table, As temp table for manipulating data before doing on actual table, copying only the structure of a table, and also copying a table from database to another database.


Use DestinationDb;

SELECT
INTO Table_new 
FROM [database].[Schema_name].OldTable;

Examples:

Here I'm using ' Mytest_db' database which has a table called 'currency'. Using below select into query we will create a backup or duplicate of currency table as currency_backup.
use Mytest_db;

SELECT *
INTO currency_backup
FROM currency;
Output:
Select * from currency_backup;

Now lets copy a table called 'Person.Address' from 'AdventureWorks2008' database to 'Mytest_db' database, Using below query.

use Mytest_db;

SELECT *
INTO Person_address
FROM AdventureWorks2008.Person.Address;

Output:
Select * from Person_address;

Now lets see this example to Copy only a few columns from more than one table into the new table where Currencykey is (6,98,16). Here we use 'AdventureWorksDW2008R2' database and tables such as DimCurrency and FactCurrencyRate; 

Select Distinct a.Currencykey,a.CurrencyName,b.AverageRate 
Into Currency2014 From DimCurrency a 
Inner Join FactCurrencyRate b 
On a.Currencykey=b.Currencykey
Where a.Currencykey in(6,98,16);

Output:
select * from Currency2014;

SELECT INTO statement can also be used to create a new, empty table(copy only structure of a table) using the schema of another; by adding a WHERE clause that causes the condition to evaluate to false. See the below query.

Select
Into Currency2014_A
From Currency2014 
Where 1=2;           //where 1=2 condition evaluate to false, so                                     no data is copied only the structure is taken 

Output:
select * from Currency2014_A;