How to Transfer Records from One Table to another in SQL Server

Several times, database administrators have needed to transfer records from one table to another table in same server, not only from one table to another in same server but also from one server to another server. We will discuss it throughout the article…

There are so many methods available for transferring records or data from one table to another, some of the methods are given below:

1. INSERT INTO Method
2. DTS Import/Export Wizard Method
3. BCP/Bulk Insert Method

INSERT INTO Method: As name suggest, use INSERT INTO transaction SQL statement to transfer records from one table to another. It is the easiest method to transfer records. This method works with within server and between the servers. Suppose you want to transfer records from Table A to Table B then the INSERT INTO SQL statement looks like:

INSERT INTO TABLE B SELECT * FROM TABLE A

Where Table A is the original table and Table B is copied table.

DTS Import/Export Wizard Method: If you do not want to write transaction SQL statement then you have another method that is DTS Import/Export wizard method. It is a graphical user interface based method. You can start your DTS import/export wizard by opening of enterprise manage. You can perform this by follow these steps:

click on "Tools" menu, place mouse on "Data Transformation Services" item in the drop down, and then after click on either "Import" or "Export Data.

BCP/Bulk Insert Method: As name suggest, this method contains two activities. One is BCP for output the data in a text file from source table and second is Bulk insert of data from the text file into the designation table.

Which is the Best Method: As we know that nothing is ideal in this world so all above discussed methods have their own good and weak point. For example, if you want to transfer huge data records from one table to another table then BCP/Bulk insert method is best for you in comparison with the INSERT INTO method. You can also try another method for transferring data from one table to another apart from above mentioned methods.