Many times one need to copy data from one part to other. SQL SELECT INTO statement is also used to serve the same purpose. This statement is to copy data from one table to other table .
Table 1: ————(copy @data)———>Table 2
SQL SELECT INTO statement could copy data from one table to other, and could also transfer data across different databases. Let us look at few examples and understand the working of SQL SELECT INTO statement.
Syntax 1:
SELECT column1, column2, column3, ... INTO newtable [IN externaldb] FROM oldtable WHERE condition;
In the following syntax “INTO” allows us to copy data from one table to another . [IN] allows us to switch databases. Values could be copied to a particular table in a particular databse with the help of "INTO" and "IN".
Syntax 2:
SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition;
- —> ‘*’ symbol helps to select all the columns in a table . Rest part is same as in syntax 1 .
SQL SELECT INTO examples:
Let us look at some examples and learn the concept of SQL SELECT INTO.
Example 1:
SELECT * INTO harshsingh FROM padhle;
The following SQL statement mentioned above creates a Backup for Padhle. Contents are copied from padhle to harshsingh.
padhle--->harshsingh @Data
Example 2: Use of [IN]:
SELECT * INTO harshsingh IN 'harshworld.mdb' FROM padhle;
Code is same as of Example ‘1’, but the content is transferred to a different database . [Database-->harshworld] Table harshsingh is in harshworld Database.
Example 3: Use of ‘WHERE’ cluase:
SELECT Id,name,marks INTO padhle IN 'harsh.mdb' FROM university WHERE Cgpa>6.5;
Table in database ‘harsh ‘ will store all the copied value , WHERE cgpa>6.5. We have had discussed the use of ‘WHERE’ clause and it is one of the most important clauses of SQL.
Example 4: Data from more than one table into a Single table:
SELECT india.curency, world.gdp INTO EconomicTimes IN 'harsh.mdb' FROM india LEFT JOIN world india.money-world.money
Joins are used to combine rows from ‘2’, or more than 2 tables. In the following example attributes from both the table are copied and transfered to a new table ('economictimes').(Values are transfer from one to other when Money attribute in both the tables matches with each other.
Hope that you like the article . give your thoughts , and drop your queries in comment section down below.