Natalia Natalie Natalia Natalie - 6 months ago 19
SQL Question

SQL Server Copying tables from one database to another

I have two databases, one is called

Natalie_playground
and one is called
LiveDB
.
Since I want to practice insert, update things, I want to copy some of the tables from the
LiveDB
to
Natalie_playground
.

The tables I want to copy are called:
Customers, Computers, Cellphones, Prices


What I tried to do is that (using SSMS) right click on a table but there is no Copy in there!

Answer

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.