A common task when using Transact-SQL (T-SQL) is to copy information from one table into another, possibly changing the data or its structure in the same operation. This can be achieved by combining the standard SELECT and INSERT commands. Copying Data Between Tables
It is common to want to copy information from one table into another using T-SQL, possibly within a stored procedure. This is useful when archiving older data from a table that is used heavily and should be kept small. It is also common to copy data into a temporary table prior to a long-running or complex operation, especially if that operation would otherwise compromise the performance of the system.
There are many ways in which information can be transferred between tables. In this article we will examine two that each require only a single line of T-SQL code.INSERT INTO SELECT
The first method of copying data is to insert data using the INSERT command but instead of providing a VALUES clause containing the information for the new row, a SELECT statement is used as a subquery. The data generated from the select statement is added into the table defined in the INSERT. Any existing rows in the target table are unaffected.
If the schemas of the two tables are identical, you can perform the operation without specifying the columns that you wish to insert into, as in the following sample code:INSERT INTO Table2
SELECT * FROM Table1 The above example will copy all of the information from Table1 into Table2. You can, of course, filter the rows with a WHERE clause, group the data or use other clauses to pre-process the information as long as the structure of the selected columns is compatible with the columns in the target table. Read more: BlackWasp
It is common to want to copy information from one table into another using T-SQL, possibly within a stored procedure. This is useful when archiving older data from a table that is used heavily and should be kept small. It is also common to copy data into a temporary table prior to a long-running or complex operation, especially if that operation would otherwise compromise the performance of the system.
There are many ways in which information can be transferred between tables. In this article we will examine two that each require only a single line of T-SQL code.INSERT INTO SELECT
The first method of copying data is to insert data using the INSERT command but instead of providing a VALUES clause containing the information for the new row, a SELECT statement is used as a subquery. The data generated from the select statement is added into the table defined in the INSERT. Any existing rows in the target table are unaffected.
If the schemas of the two tables are identical, you can perform the operation without specifying the columns that you wish to insert into, as in the following sample code:INSERT INTO Table2
SELECT * FROM Table1 The above example will copy all of the information from Table1 into Table2. You can, of course, filter the rows with a WHERE clause, group the data or use other clauses to pre-process the information as long as the structure of the selected columns is compatible with the columns in the target table. Read more: BlackWasp
Posts
0 comments:
Post a Comment