How to: Copy Columns from One Table to Another
You can copy columns from one table to another, copying either just the column definition, or the definition and data.
Note
A new version of Table Designer appears for databases in the SQL Server 2012 format. This topic describes the old version of Table Designer, which you use with databases in earlier formats of SQL Server.
In the new version, you can change a table definition through a graphical interface or directly in a script pane. If you use the graphical interface, the table’s definition is automatically updated in the script pane. To apply the SQL code in the script pane, choose the Update button. For more information about the new version, see How to: Create Database Objects Using Table Designer.
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Customizing Development Settings in Visual Studio.
To copy column definitions from one table to another
Open the table with columns you want to copy and the one you want to copy into.
Click the tab for the table with the columns you want to copy and select those columns.
From the Edit menu, click Copy.
Click the tab for the table into which you want to copy the columns.
Select the column you want to follow the inserted columns and from the Edit menu, click Paste.
When you copy a database column that has an alias data type from one database to another, the user-defined 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. For more information about alias data types, see the Microsoft Web site.
To copy data from one table to another
Follow the directions for copying column definitions above.
Note
Before you begin to copy data from one table to another, make sure that the data types in the destination columns are compatible with the data types of the source columns
In Server Explorer, right-click the Tables node and click New Query.
From the Query Designer menu, point to Change Type, and then click Insert Results.
In the Choose Target Table for Insert Results dialog box, select the table into which you want to copy the data, and then click OK.
If you are copying rows within a table, you can add the source table as a destination table.
Note
Query Designer cannot determine in advance which tables and views you can update. Therefore, the list of tables in the Choose Target Table for Insert Results dialog box shows all available tables and views in the data connection you are querying, even those that you might not be able to copy rows to.
Right-click in the body of the diagram pane and, from the shortcut menu, click Add Table to Diagram.
In the Add Table dialog box, select each table from which you want to copy data, click Add, and then click Close.
The tables, in an abbreviated form, appear in the diagram pane.
In the abbreviated tables, check the boxes for any columns from which you want to copy data.
In the criteria pane, in the Append column, for each target column choose a column from which you want to copy data.
Specify the rows to copy by entering search conditions in the criteria pane. For details, see How to: Specify Search Conditions.
If you do not specify a search condition, all rows from the source table will be copied to the destination table.
If you want to copy summary information, specify Group By options. For details, see How to: Summarize or Aggregate Values for All Rows in a Table.
Click the Execute SQL button to run the query.
When you execute an insert results query, no results are reported in the Results pane. Instead, a message appears indicating how many rows were copied.
When you copy a database column that has an alias data type from one database to another, the user-defined 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. For more information about alias data types, see the Microsoft Web site.
See Also
Tasks
Concepts
Tables (Visual Database Tools)