将包含数据的表复制到 SQL Server 2008 中的另一个数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14065580/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
copy tables with data to another database in SQL Server 2008
提问by prabu R
I have to copy the tables with data from one database to anotherusing Query. I know how to copy tables with data within a database. But I was not sure about how to do the same for copying between two databases.
我必须使用Query将包含数据的表从一个数据库复制到另一个数据库。我知道如何在数据库中复制包含数据的表。但我不确定如何在两个数据库之间进行复制。
I have to copy huge number of tables, so I need any fast method using query...
我必须复制大量表，所以我需要使用查询的任何快速方法......
Anybody please help out...Thanks in advance...
任何人请帮忙...提前致谢...
回答by Mahmoud Gamal
You can use the same way to copy the tables within one database, the SELECT INTObut use a fully qualified tables names database.schema.object_nameinstead like so:
您可以使用相同的方式复制一个数据库中的表，SELECT INTO但使用完全限定的表名称，database.schema.object_name如下所示：
USE TheOtherDB;
SELECT *
INTO NewTable
FROM TheFirstDB.Schemaname.OldTable
This will create a new table Newtablein the database TheOtherDBfrom the table OldTablewhih belongs to the databaseTheFirstDB
这将创建一个新的表Newtable在数据库TheOtherDB从表OldTablewhih属于数据库TheFirstDB
回答by Raj
- Right click on the database, select tasks and click on Generate Scripts.
- In the resultant pop-up, choose options as required (click advanced), to drop and create table, drop if exists, etc.
- Scroll down and choose "Schema and Data" or "Data Only" or "Types of data to script (2008 R2)"as required.
- Save to file and execute on the destination DB.
- 右键单击数据库，选择任务并单击生成脚本。
- 在结果弹出窗口中，根据需要选择选项（单击高级）、删除和创建表、删除（如果存在）等。
- 向下滚动并根据需要选择“Schema and Data”或“Data Only”或“Types of data to script (2008 R2)”。
- 保存到文件并在目标数据库上执行。
Advantages -
好处 -
- Can be executed against the destination DB, even if it is on another server / instance
- Quickly script multiple tables, with data as needed
- 可以针对目标数据库执行，即使它在另一个服务器/实例上
- 快速编写多个表的脚本，根据需要使用数据
Warning - Might take quite a while to script, if the tables contain a large amount of data.
警告 - 如果表包含大量数据，则编写脚本可能需要很长时间。
Rajan
拉詹
回答by Praveen Mitta
Below SQL Query will copy SQL Server table schema & data from one database to another database. You can always table name (SampleTable) in your destination database.
下面的 SQL 查询会将 SQL Server 表架构和数据从一个数据库复制到另一个数据库。您始终可以在目标数据库中使用表名 (SampleTable)。
SELECT * INTO DestinationDB.dbo.SampleTable FROM SourceDB.dbo.SampleTable
回答by Adeel Ahmed
INSERT INTO DB2.dbo.MyOtherTable (Col0, Col1)
SELECT Col0, Col1 FROM DB1.dbo.MyTable
Both table column's must have same data types..
两个表列必须具有相同的数据类型。
