在 SQL Server 中将表从一个数据库复制到另一个数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/187770/
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 from one database to another in SQL Server
提问by RyanKeeter
I have a database called foo and a database called bar. I have a table in foo called tblFoobar that I want to move (data and all) to database bar from database foo. What is the SQL statement to do this?
我有一个名为 foo 的数据库和一个名为 bar 的数据库。我在 foo 中有一个名为 tblFoobar 的表，我想将（数据和所有）从数据库 foo 移动到数据库 bar。执行此操作的 SQL 语句是什么？
采纳答案by Amy B
On SQL Server? and on the same database server? Use three part naming.
在 SQL Server 上？并且在同一个数据库服务器上？使用三部分命名。
INSERT INTO bar..tblFoobar( *fieldlist* )
SELECT *fieldlist* FROM foo..tblFoobar
This just moves the data. If you want to move the table definition (and other attributes such as permissions and indexes), you'll have to do something else.
这只是移动数据。如果要移动表定义（以及其他属性，例如权限和索引），则必须执行其他操作。
回答by David
SQL Server Management Studio's "Import Data" task (right-click on the DB name, then tasks) will do most of this for you. Run it from the database you want to copy the data into.
SQL Server Management Studio 的“导入数据”任务（右键单击数据库名称，然后单击任务）将为您完成大部分工作。从要将数据复制到的数据库运行它。
If the tables don't exist it will create them for you, but you'll probably have to recreate any indexes and such. If the tables do exist, it will append the new data by default but you can adjust that (edit mappings) so it will delete all existing data.
如果表不存在，它会为您创建它们，但您可能必须重新创建任何索引等。如果表确实存在，默认情况下它会附加新数据，但您可以调整它（编辑映射），以便删除所有现有数据。
I use this all the time and it works fairly well.
我一直在使用它，而且效果很好。
回答by leoinfo
This should work:
这应该有效：
SELECT *
INTO DestinationDB..MyDestinationTable
FROM SourceDB..MySourceTable
It will notcopy constaints, defaults or indexes. The table created will nothave a clustered index.
它不会复制约束、默认值或索引。创建的表将没有聚集索引。
Alternatively you could:
或者你可以：
INSERT INTO DestinationDB..MyDestinationTable
SELECT * FROM SourceDB..MySourceTable
If your destination table exists and is empty.
如果您的目标表存在并且为空。
回答by Igor Voplov
If it's one table only then all you need to do is
如果只有一张桌子，那么您需要做的就是
- Script table definition
- Create new table in another database
- Update rules, indexes, permissions and such
- Import data (several insert into examples are already shown above)
- 脚本表定义
- 在另一个数据库中创建新表
- 更新规则、索引、权限等
- 导入数据（上面已经显示了几个插入示例）
One thing you'll have to consider is other updates such as migrating other objects in the future. Note that your source and destination tables do not have the same name. This means that you'll also have to make changes if you dependent objects such as views, stored procedures and other.
您必须考虑的一件事是其他更新，例如将来迁移其他对象。请注意，源表和目标表的名称不同。这意味着如果您依赖于诸如视图、存储过程等的对象，您还必须进行更改。
Whit one or several objects you can go manually w/o any issues. However, when there are more than just a few updates 3rd party comparison tools come in very handy. Right now I'm using ApexSQL Difffor schema migrations but you can't go wrong with any other tool out there.
您可以手动处理一个或多个对象，而不会出现任何问题。但是，当更新不止几个时，第 3 方比较工具就派上用场了。现在我正在使用ApexSQL Diff进行架构迁移，但使用任何其他工具都不会出错。
回答by ScottStonehouse
Script the
create tablein management studio, run that script in bar to create the table. (Right click table in object explorer, script table as, create to...)INSERT bar.[schema].table SELECT * FROM foo.[schema].table
脚本的
create table管理工作室，运行该脚本在酒吧创建表。（右键单击对象资源管理器中的表，脚本表为，创建为...）INSERT bar.[schema].table SELECT * FROM foo.[schema].table
回答by ryan
You can also use the Generate SQL Server Scripts Wizardto help guide the creation of SQL script's that can do the following:
您还可以使用生成 SQL Server 脚本向导来帮助指导可以执行以下操作的 SQL 脚本的创建：
- copy the table schema
- any constraints (identity, default values, etc)
- data within the table
- and many other options if needed
- 复制表模式
- 任何约束（身份、默认值等）
- 表内数据
- 如果需要，还有许多其他选择
Good example workflow for SQL Server 2008with screen shots shown here.
SQL Server 2008 的良好示例工作流，此处显示屏幕截图。
回答by NeverHopeless
You may go with this way: ( a general example )
您可以采用这种方式：（一般示例）
insert into QualityAssuranceDB.dbo.Customers (columnA, ColumnB)
Select columnA, columnB from DeveloperDB.dbo.Customers
Also if you need to generate the column names as well to put in insert clause, use:
此外，如果您还需要生成列名以放入插入子句，请使用：
select (name + ',') as TableColumns from sys.columns
where object_id = object_id('YourTableName')
Copy the result and paste into query window to represent your table column names and even this will exclude the identity column as well:
复制结果并粘贴到查询窗口以表示您的表列名称，甚至这也会排除标识列：
select (name + ',') as TableColumns from sys.columns
where object_id = object_id('YourTableName') and is_identity = 0
Remember the script to copy rows will work iff the databases belongs to the same location.
请记住，如果数据库属于同一位置，则复制行的脚本将起作用。
You can Try This.
你可以试试这个。
select * into <Destination_table> from <Servername>.<DatabaseName>.dbo.<sourceTable>
Server name is optional if both DB is in same server.
如果两个数据库位于同一服务器中，则服务器名称是可选的。
回答by Mohan
If there is existing table and we wants to copy only data, we can try this query.
如果存在现有表，而我们只想复制数据，则可以尝试此查询。
insert into Destination_Existing_Tbl select col1,col2 FROM Source_Tbl
插入 Destination_Existing_Tbl select col1,col2 FROM Source_Tbl
回答by Arun Prasad E S
Copy Data
复制数据
INSERT INTO Alfestonline..url_details(url,[status],recycle)
SELECT url,status,recycle FROM AlfestonlineOld..url_details
