SQL 将数据从一个数据库传输到另一个数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10028805/
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
Transfer data from one database to another database
提问by Bharathi
how to fetch the data from one database and insert in to another database table....I can't to do this. Please help me to for transferring Data from on to another. thanks in advance...
如何从一个数据库中获取数据并插入到另一个数据库表中......我不能这样做。请帮助我将数据从一个传输到另一个。提前致谢...
回答by Virus
http://okayguru.blogspot.co.uk/2012/03/copy-one-database-to-another-database.html
http://okayguru.blogspot.co.uk/2012/03/copy-one-database-to-another-database.html
There are several ways to do this, below are two options:
有几种方法可以做到这一点，下面是两个选项：
Option 1 - Right click on the database you want to copy
选项 1 - 右键单击要复制的数据库
Choose 'Tasks' > 'Generate scripts'
'Select specific database objects'
Check 'Tables'
Mark 'Save to new query window'
Click 'Advanced'
Set 'Types of data to script' to 'Schema and data'
Next, Next
选择“任务”>“生成脚本”
'选择特定的数据库对象'
检查“表”
标记“保存到新的查询窗口”
点击“高级”
将“脚本的数据类型”设置为“架构和数据”
下一个，下一个
You can now run the generated query on the new database.
您现在可以在新数据库上运行生成的查询。
Option 2
选项 2
Right click on the database you want to copy
'Tasks' > 'Export Data'
Next, Next
Choose the database to copy the tables to
Mark 'Copy data from one or more tables or views'
Choose the tables you want to copy
Finish
右键单击要复制的数据库
“任务”>“导出数据”
下一个，下一个
选择要将表复制到的数据库
标记“从一个或多个表或视图复制数据”
选择要复制的表
结束
回答by Lokanathan
Example for insert into values in One database table into another database table
将一个数据库表中的值插入到另一个数据库表中的示例
insert into dbo.onedatabase.FolderStatus
(
[FolderStatusId],
[code],
[title],
[last_modified]
)
select [FolderStatusId], [code], [title], [last_modified]
from dbo.Twodatabase.f_file_stat
回答by pimbrouwers
For those on Azure, follow these modified instructions from Virus:
对于 Azure 上的用户，请按照 Virus 中的这些修改后的说明进行操作：
- Open SSMS.
- Right-click the Database you wish to copy data from.
- Select Generate Scripts>> Select Specific Database Objects>> Choose the tables/object you wish to transfer. strong text
- In the "Save to file" pane, click Advanced
- Set "Types of data to script" to Schema and data
- Set "Script DROP and CREATE" to Script DROP and CREATE
- Under "Table/View Options" set relevant items to TRUE. Though I recommend setting all to TRUE just in case. You can always modify the script after it generates.
- Set filepath >> Next >> Next
- Open newly created SQL file. Remove "Use" from top of file.
- Open new query window on destination database, paste script contents (without using) and execute.
- 打开 SSMS。
- 右键单击要从中复制数据的数据库。
- 选择生成脚本>>选择特定数据库对象>> 选择要传输的表/对象。 强文本
- 在“保存到文件”窗格中，单击高级
- 将“要脚本的数据类型”设置为架构和数据
- 将“Script DROP and CREATE”设置为Script DROP and CREATE
- 在“表/视图选项”下将相关项目设置为 TRUE。虽然我建议将 all 设置为 TRUE 以防万一。您始终可以在脚本生成后对其进行修改。
- 设置文件路径 >> 下一步 >> 下一步
- 打开新创建的 SQL 文件。从文件顶部删除“使用”。
- 在目标数据库上打开新的查询窗口，粘贴脚本内容（不使用）并执行。
回答by Zilog
if both databases are on same server and you want to transfer entire table (make copy of it) then use simple select into statement ...
如果两个数据库都在同一台服务器上，并且您想传输整个表（制作它的副本），则使用简单的 select into 语句...
select * into anotherDatabase..copyOfTable from oneDatabase..tableName
You can then write cursor top of sysobjects and copy entire set of tables that way.
然后，您可以将光标写入 sysobjects 的顶部并以这种方式复制整个表集。
If you want more complex data extraction & transformation, then use SSIS and build appropriate ETL in it.
如果您想要更复杂的数据提取和转换，请使用 SSIS 并在其中构建适当的 ETL。
回答by e4rthdog
- You can backup and restore the database using Management Studio.
- Again from Management Studio you can use "copy database".
- you can even do it manually if there is a reason to do so. I mean manually create the target db and manually copying data by sql statements...
- 您可以使用 Management Studio 备份和恢复数据库。
- 再次从 Management Studio，您可以使用“复制数据库”。
- 如果有理由，您甚至可以手动执行此操作。我的意思是手动创建目标数据库并通过 sql 语句手动复制数据...
can you clarify why you ask this? Is it that you dont have expierience in doing it or something else?
你能澄清你为什么问这个吗？是你没有经验做这件事还是别的什么？
回答by Pavan Shevle
You can use visual studio 2015. Go to Tools=> SQL server=> New Data comparison
您可以使用 Visual Studio 2015。转到工具=> SQL 服务器=>新数据比较
Select sourceand targetDatabase.
选择源和目标数据库。
回答by Coder
There are multiple options and depend on your needs. See the following links:
有多种选择，取决于您的需求。请参阅以下链接：
回答by Anubis
These solutions are working in case when target database is blank. In case when both databases already have some data you need something more complicated http://byalexblog.net/merge-sql-databases
这些解决方案适用于目标数据库为空的情况。如果两个数据库都已经有一些数据，你需要更复杂的东西http://byalexblog.net/merge-sql-databases
回答by themefield
This can be achieved by a T-SQL statement, if you are aware that FROMclause can specify database for table name.
这可以通过 T-SQL 语句来实现，如果您知道该FROM子句可以为表名指定数据库。
insert into database1..MyTable
select from database2..MyTable
Here is how Microsoft explains the syntax: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15
以下是微软对语法的解释：https: //docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15
If the table or view exists in another database on the same instance of SQL Server, use a fully qualified name in the form
database.schema.object_name.
如果表或视图存在于同一 SQL Server 实例上的另一个数据库中，请使用格式为 的完全限定名称
database.schema.object_name。
schema_namecan be omitted, like above, which means the default schema of the current user. By default, it's dbo.
schema_name可以省略，就像上面一样，这意味着当前用户的默认架构。默认情况下，它是dbo.
Add any filtering to columns/rows if you want to. Be sure to create any new table before moving data.
如果需要，可以向列/行添加任何过滤。确保在移动数据之前创建任何新表。
