How to copy data from one table to another in SQL Server

When developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to copy data from one table to another, they should follow the next steps:

1. Follow the steps described in the article: How to copy column definitions from one table to another in SQL Server. Developers should be sure that the data types in the destination columns are compatible with the data types of the source columns.

2. In Server Explorer, right-click the Tables node and click New Query.

3. From the Query Designer menu, point to Change Type, and then click Insert Results.

4. In the Choose Target Table for Insert Results dialog box, select the table into which they want to copy the data, and then click OK.

If they are copying rows within a table, they can add the source table as a destination table.

5. Right-click in the body of the diagram pane and, from the shortcut menu, click Add Table to Diagram.

6. In the Add Table dialog box, select each table from which they want to copy data, click Add, and then click Close.

The tables, in an abbreviated form, appear in the diagram pane.

7. In the abbreviated tables, check the boxes for any columns from which they want to copy data.

8. In the criteria pane, in the Append column, for each target column choose a column from which they want to copy data.

9. Specify the rows to copy by entering search conditions in the criteria pane

If they do not specify a search condition, all rows from the source table will be copied to the destination table.

10. If they want to copy summary information, specify Group By options.

11. Click the Execute SQL button to run the query.

When developers copy a column that has an alias data type from one database to another, the alias data type may not be available in the destination database. In such a case, the column will be assigned the nearest matching base data type available in that database.

to copy column definitions from one table to another in SQL Server. Developers should be sure that the data types in the destination columns are compatible with the data types of the source columns.

2. In Server Explorer, right-click the Tables node and click New Query.

3. From the Query Designer menu, point to Change Type, and then click Insert Results.

4. In the Choose Target Table for Insert Results dialog box, select the table into which they want to copy the data, and then click OK.

If they are copying rows within a table, they can add the source table as a destination table.

5. Right-click in the body of the diagram pane and, from the shortcut menu, click Add Table to Diagram.

6. In the Add Table dialog box, select each table from which they want to copy data, click Add, and then click Close.

The tables, in an abbreviated form, appear in the diagram pane.

7. In the abbreviated tables, check the boxes for any columns from which they want to copy data.

8. In the criteria pane, in the Append column, for each target column choose a column from which they want to copy data.

9. Specify the rows to copy by entering search conditions in the criteria pane

If they do not specify a search condition, all rows from the source table will be copied to the destination table.

10. If they want to copy summary information, specify Group By options.

11. Click the Execute SQL button to run the query.

When developers copy a column that has an alias data type from one database to another, the alias data type may not be available in the destination database. In such a case, the column will be assigned the nearest matching base data type available in that database.