SQL Server Copying tables from one database to another

Question

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!

In both databases exists the same tables (with the same structure)? only you want pass only a data? — Gaston Flores, Jul 11 '13 at 00:13
possible duplicate of [SQL Server 2008: copying the contents of all tables from one database into another database](http://stackoverflow.com/questions/4028104/sql-server-2008-copying-the-contents-of-all-tables-from-one-database-into-anoth) and [How to copy one table from a database to another database/table in SQL Server](http://stackoverflow.com/q/187770/62576), both of which are listed in the `Related` list to the right of your question. ===>>> Please do at least a basic search here before posting a new question to see if it has already been asked (and answered) before. Thanks. — Ken White, Jul 11 '13 at 00:24
@natalia, Ken have reason, for this question, normally the answers already exists. Thanks. — Gaston Flores, Jul 11 '13 at 00:42
I already did that research, as a beginner I did not know what is that .. in the syntax of: How to copy one table from a database to another database/table in SQL Server. So this is why I asked and got a syntax at least I can understand. — Natalia Natalie, Jul 11 '13 at 15:16
take a look at David's answer: http://stackoverflow.com/questions/187770/copy-tables-from-one-database-to-another-in-sql-server — Ellioth Velasquez, Apr 06 '15 at 18:38

Gaston Flores · Accepted Answer · 2013-07-11T00:34:54.393

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.

Can you do this without selecting the field names explicitly? — Adam Hughes, Jan 20 '17 at 19:58

score 9 · Answer 2 · answered Jul 11 '13 at 00:28

9

Right click on your database -> under Tasks choose Generate scripts, follow the wizard, choose your tables and check the check box that says 'script table data' (or similar) generate it to an SQL script and execute it on your other DB.

answered Jul 11 '13 at 00:28

Ron.B.I

2,726
1
20
27

score 4 · Answer 3 · answered Jul 11 '13 at 09:24

4

You can also try SQL Server Import/Export wizard. If target tables do not exist already they will be created when you run the wizard.

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

answered Jul 11 '13 at 09:24

Timothy Walden

676
6
3

Probably the simplest and least time consuming. +1 – LegendaryDude Oct 30 '15 at 14:58

score 1 · Answer 4 · edited Apr 02 '15 at 20:51

1

I found an easy way from other blog. Hope this might 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

edited Apr 02 '15 at 20:51

Alexander Vogt

17,879
13
52
68

answered Apr 02 '15 at 20:33

Learner

19
1

score 1 · Answer 5 · answered Apr 09 '17 at 09:44

Try this:

If target table is exists :

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

And if target table is not exists :

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

Good Luck!

score 0 · Answer 6 · edited Jul 14 '16 at 11:38

0

try this

USE TargetDatabase
GO

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

edited Jul 14 '16 at 11:38

Paritosh

11,144
5
56
74

answered Jun 10 '14 at 07:23

Mahmoude Elghandour

2,921
1
22
27

score 0 · Answer 7 · answered May 23 '16 at 04:31

0

If you want to copy only the schema of tables, you can add a false condition to the end of mentioned 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

answered May 23 '16 at 04:31

Emad Armoun

1,625
2
19
32

SQL Server Copying tables from one database to another

7 Answers7

Linked