SQL Server Copying tables from one database to another

Question

I have two databases, one called Natalie_playground and one called LiveDB . Since I want to practice insertion, update things, I want to copy some of the tables from LiveDB to Natalie_playground .

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

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

Answer 1

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

• If the goal table does not exist, it will create the following script (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 a goal 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 to learn and apply this, you can use the previous scripts, but if you want to copy the entire structure and data from the database to another, you should use "Backing up and restoring the database" or "Create a script Database with data" and run it in another database.

Answer 2

Right-click on your database → under Tasks, select Generate scripts , follow the instructions of the wizard, select your tables and check the 'script table data (or similar) generate it in SQL script and execute it on another database.

Answer 3

You can also try the SQL Server Import / Export Wizard. If the target tables do not exist, they will be created when the wizard starts.

Check out MSDN for more details http://msdn.microsoft.com/en-us/library/ms141209.aspx

Answer 4

I found an easy way from another blog. Hope this can be helpful.

 Select * into DestinationDB.dbo.tableName from SourceDB.dbo.SourceTable 

http://www.codeproject.com/Tips/664327/Copy-Table-Schema-and-Data-From-One-Database-to-An

Answer 5

Try the following:

If a goal table exists:

 SELECT SourceTableAlias.* INTO TargetDB.dbo.TargetTable FROM SourceDB.dbo.SourceTable SourceTableAlias 

And if the target table does not exist:

  INSERT INTO TargetDB.dbo.TargetTable SELECT SourceTableAlias.* FROM SourceDB.dbo.SourceTable SourceTableAlias 

Good luck

Answer 6

try it

 USE TargetDatabase GO INSERT INTO dbo.TargetTable(field1, field2, field3) SELECT field1, field2, field3 FROM SourceDatabase.dbo.SourceTable WHERE (some condition) 

Answer 7

If you want to copy only the table schema, you can add a false condition to the end of the specified queries.

ex.

 SELECT table_A.FIELD_1, ..., table_A.FIELD_N INTO LiveDB.custom_table FROM Natalie_playground.dbo.custom_table table_A WHERE 0 > 1