Transferring Data from One Table to Another with query
Larsen Every DBA needs to transfer data between tables. This is the kind of task that comes up all the time. Not only do DBAs need to know how to transfer data between two tables on the same server, but they will also need to know how to do it from one server to another. This article will look at a number of different methods to transfer data from one table to another.
The INSERT INTO Method This method, as the heading suggests, uses the INSERT INTO T-SQL statement to move records from one table to another. This method is one of the easiest methods of transferring data. This method can not only be done between tables on the same server, but can also be done across servers. To use this method, all you have to do is code an INSERT INTO statement that identifies the target table, and uses a SELECT statement to identify the data you want to copy from the source table. Let me show you an example.
Say you have two tables TABLE1, and TABLE2, where both tables have exactly the some table structure and are in the same database. In this case, to copy all the rows from TABLE1 to TABLE2 you would use the following INSERT INTO statement: INSERT INTO TABLE2 SELECT * FROM TABLE1 Now suppose you do not want to copy all the rows, but only those rows that meet a specific criteria. Say you only want to copy those rows where COL1 is equal to "A." To do this you would just modify the above code to look like this: INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A'
See how simple it is to copy data from one table to another using the INSERT INTO method? You can even copy a selected set of columns, if you desire, by identifying the specific columns you wish to copy and populate, like so: INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1 The above command copies only data from columns COL1, COL4, and COL7 in TABLE1 to COL1, COL2, and COL3 in TABLE2.
This method can also be used to copy data from one database to another. To do this you just need to fully qualify .the source and target table names on the INSERT INTO statement to identify which database you want to use. You can copy data from one server to another the same way by using fully qualified linked server names
Larsen Every DBA needs to transfer data between tables. This is the kind of task that comes up all the time. Not only do DBAs need to know how to transfer data between two tables on the same server, but they will also need to know how to do it from one server to another. This article will look at a number of different methods to transfer data from one table to another.
The INSERT INTO Method This method, as the heading suggests, uses the INSERT INTO T-SQL statement to move records from one table to another. This method is one of the easiest methods of transferring data. This method can not only be done between tables on the same server, but can also be done across servers. To use this method, all you have to do is code an INSERT INTO statement that identifies the target table, and uses a SELECT statement to identify the data you want to copy from the source table. Let me show you an example.
Say you have two tables TABLE1, and TABLE2, where both tables have exactly the some table structure and are in the same database. In this case, to copy all the rows from TABLE1 to TABLE2 you would use the following INSERT INTO statement: INSERT INTO TABLE2 SELECT * FROM TABLE1 Now suppose you do not want to copy all the rows, but only those rows that meet a specific criteria. Say you only want to copy those rows where COL1 is equal to "A." To do this you would just modify the above code to look like this: INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A'
See how simple it is to copy data from one table to another using the INSERT INTO method? You can even copy a selected set of columns, if you desire, by identifying the specific columns you wish to copy and populate, like so: INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1 The above command copies only data from columns COL1, COL4, and COL7 in TABLE1 to COL1, COL2, and COL3 in TABLE2.
This method can also be used to copy data from one database to another. To do this you just need to fully qualify .
Comments
Post a Comment