SQL Server将表从一个数据库复制到另一个数据库

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.

我有两个数据库，一个叫做Natalie_playground，另一个叫做LiveDB。由于我想练习插入，更新内容，我想将一些表从LiveDB复制到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!

我试图做的是（使用SSMS）右键单击一个表，但那里没有Copy！

7 个解决方案

#1

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

假设您有两个数据库，例如A和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.

注意：如果您想学习并练习它，可以使用以前的脚本，但如果要将完整的结构和数据从数据库复制到另一个脚本，则应使用“备份和恢复数据库”或“使用数据生成脚本数据库”并将其运行到另一个数据库中。

#2

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.

右键单击数据库 - >在任务下选择生成脚本，按照向导，选择表并选中“脚本表数据”（或类似）的复选框，将其生成为SQL脚本并在其他数据库上执行。

#3

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.

您还可以尝试SQL Server导入/导出向导。如果目标表不存在，则在运行向导时将创建它们。

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

有关更多详细信息，请访问MSDN http://msdn.microsoft.com/en-us/library/ms141209.aspx

#4

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

#5

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!

祝你好运！

#6

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

#7

try this

尝试这个

USE TargetDatabase
GO

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

#1