Tuesday, March 14, 2017

SQL SELECT INTO

In SQL Server, there may be a requirement when user wants to transfer or copy the result-set or data from one table into another table. Depending upon the requirement of the situation, the data transfer may be between the temporary tables or the existing tables. To accomplish this, SELECT INTO is used.



SELECT INTO creates a new table with the schema of the SELECT statement and populates it with the result-set of the SELECT statement. It can also be used to combine the data from different tables into one table. If the table name specified in the SELECT INTO is already present in the database, the query will not execute and results in error.



Syntax of SELECT INTO:
           
      SELECT select_column_list
      INTO NEW_TABLE
      FROM TABLE_NAME
      [CLAUSE(S)];

[] = Optional code

select_column_list is the list of columns for which the data is to be listed.

NEW_TABLE is name of the table to be created with the selected expressions and their associated definitions (new_table must not exist).

FROM specifies the location (TABLE_NAME i.e. database table) from where the data is retrieved.

CLAUSE(S) specifies the search conditions for the rows returned by the query. It may include following clauses:

·         WHERE clause
·         GROUP BY clause
·         HAVING clause
·         ORDER BY clause


The structure of the new table is defined by the data types of the columns in the select list. If user only needs to creates the scheme into the new table without copying the result-set, then he/she needs to specify some false condition in the WHERE clause like 1 = 2.





Features of SELECT INTO:

· SELECT INTO does not required the destination table to be exist in the database.
· It will create a new table when the command is executed.
· It also automatically creates the structure of the destination table as per the columns in the SELECT query.

Share This
Previous Post
Next Post

TekGrabs, established in 2017, is in the business of sharing Information. It provides news, articles and photos about the different technologies, inventions in different domains. Provides information on programming and databases.

0 comments: