在SQL Server中从一个数据库复制表到另一个数据库。

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移动(数据和所有数据)到数据库栏。什么是SQL语句?

9 个解决方案

#1

192

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.

这只是移动数据。如果您想要移动表定义(以及其他属性，如权限和索引)，则必须执行其他操作。

#2

522

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的“导入数据”任务(右击DB名称，然后任务)将为您完成大部分任务。从您想要复制数据的数据库中运行它。

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.

我一直用这个，而且效果很好。

#3

This should work:

这应该工作:

SELECT * 
INTO DestinationDB..MyDestinationTable 
FROM SourceDB..MySourceTable

It will not copy constaints, defaults or indexes. The table created will not have a clustered index.

它不会复制const、默认值或索引。创建的表没有聚集索引。

Alternatively you could:

或者你可以:

INSERT INTO DestinationDB..MyDestinationTable 
SELECT * FROM SourceDB..MySourceTable

If your destination table exists and is empty.

如果您的目标表存在并且是空的。

#4

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 Diff for schema migrations but you can’t go wrong with any other tool out there.

一个或几个对象，你可以手动操作任何问题。然而，当有不止几个更新时，第三方比较工具就派上了用场。现在我正在使用ApexSQL Diff进行模式迁移，但是您不能在任何其他工具上出错。

#5

Script the create table in management studio, run that script in bar to create the table. (Right click table in object explorer, script table as, create to...)

在管理studio中编写创建表，在bar中运行该脚本以创建表。(在对象资源管理器中右键单击表，将脚本表创建为…)
INSERT bar.[schema].table SELECT * FROM foo.[schema].table

插入杆。(模式)。表SELECT * FROM foo.[schema].table。

#6

You can also use the Generate SQL Server Scripts Wizard to help guide the creation of SQL script's that can do the following:

您还可以使用Generate SQL Server Scripts向导来帮助指导创建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 2008 with screen shots shown here.

这里显示了SQL Server 2008的良好示例工作流。

#7

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.

如果两个数据库都在同一个服务器中，服务器名称是可选的。

#8

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_tbl中，从Source_Tbl中选择col1,col2。

#9

Copy Data

复制数据

INSERT INTO Alfestonline..url_details(url,[status],recycle) 
SELECT url,status,recycle FROM AlfestonlineOld..url_details

#1

192