Copy Data from a Table in One Database to Another Separate Database

Copy Data from a table in one Database to another separate database

SELECT ... INTO creates a new table. You'll need to use INSERT. Also, you have the database and owner names reversed.

INSERT INTO DB1.dbo.TempTable
SELECT * FROM DB2.dbo.TempTable

Transfer data from one database to another database

There are several ways to do this, below are two options:

Option 1
- Right click on the database you want to copy

• Choose 'Tasks' > 'Generate scripts'

• 'Select specific database objects'

• Check 'Tables'

• Mark 'Save to new query window'

• Click 'Advanced'

• Set 'Types of data to script' to 'Schema and data'

• Next, Next

You can now run the generated query on the new database.

Option 2

• Right click on the database you want to copy

• 'Tasks' > 'Export Data'

• Next, Next

• Choose the database to copy the tables to

• Mark 'Copy data from one or more tables or views'

• Choose the tables you want to copy

• Finish

how to copy only data from one database table to another database existing table in sql server

Try this ...

INSERT INTO DataBase2.dbo.table2
SELECT * FROM DataBase1.dbo.table1

SQL Server Copying tables from one database to another

Assuming that you have two databases, for example A and B:

• If target table not exists, the following script will create (I do not recommend this way):

  SELECT table_A.FIELD_1, table_A.FIELD_2,......, table_A.FIELD_N 
  INTO COPY_TABLE_HERE 
  FROM  A.dbo.table_from_A table_A
  

• If target table exists, then:

   INSERT INTO TABLE_TARGET 
   SELECT table_A.FIELD_1, table_A.FIELD_2,......, table_A.FIELD_N 
   FROM  A.dbo.table_from_A table_A
  

Note: if you want learn and practice this, you can use previous scripts, but if you want copy the complete structure and data from database to another, you should use, "Backup and restore Database" or, "Generate Script Database with data" and run this into another database.

Copy one table data to another database table in same server Azure Services

You can use the bcp utility to export the table to a local drive in your computer.

bcp NLayerApp.dbo.Customer out "C:\MyFolderPath\Customer.txt" -T -c -S WIN7VS2010RC1\SQLEXPRESS

Then you can import it to the other database using the same utility.

bcp TestDB.dbo.Customer in "C:\MyFolderPath\Customer.txt" -c -U mysqlazureuser@mysqlazureservername -S tcp:mysqlazureservername.database.windows.net -P mypassword

You can learn more about bcp here.

Copy table from one database to another

Create a linked server to the source server. The easiest way is to right click "Linked Servers" in Management Studio; it's under Management -> Server Objects.

Then you can copy the table using a 4-part name, server.database.schema.table:

select  *
into    DbName.dbo.NewTable
from    LinkedServer.DbName.dbo.OldTable

This will both create the new table with the same structure as the original one and copy the data over.

Copy a table from one database to another in Postgres

Extract the table and pipe it directly to the target database:

pg_dump -t table_to_copy source_db | psql target_db

Note: If the other database already has the table set up, you should use the -a flag to import data only, else you may see weird errors like "Out of memory":

pg_dump -a -t table_to_copy source_db | psql target_db

Copy tables from one database to another in SQL Server

On SQL Server? and on the same database server? Use three part naming.

INSERT INTO bar..tblFoobar( *fieldlist* )
SELECT *fieldlist* FROM foo..tblFoobar

This just moves the data. If you want to move the table definition (and other attributes such as permissions and indexes), you'll have to do something else.

---

Related Topics