Recently I’ve to copy tables from one database to another in the same SQL Server. It’s quiet simple to do it using select query. All you hae to do is to use the SELECT INTO query along with the three-part naming for the table. method will create the table structure in the destination database and copies the data to the newly created table in the destination DB. With this query you can use where clause to filter the records you want to copy. You can specify the column names to copy only the required columns of data to the other database. However, the schema, primary key, foreign keys, constraints, triggers and indexes will not get copied. You need to create them separately. Here is an example to copy a table to another database.
SELECT * INTO [Test.MyTecBits.com].dbo.PurchaseOrders FROM WideWorldImporters.Purchasing.PurchaseOrders GO;
This method will help you to copy tables between databases on the same SQL Server. To copy tables from the databases residing in different SQL Servers, you need to use the linked server technique.
- About SELECT INTO at Microsoft Docs.