如何将表从一个 sql server 数据库移动到另一个？

How to move tables from one sql server database to another?

提问by Jeff Widmer

We have a database that has grown to about 50GB and we want to pull out a certain set of tables (about 20 of them) from within that database and move them into a new database. All of this would be on the same SQL Server. The tables that we want to pull out are about 12GB of space (6GB data, 6GB indexes).

我们有一个已经增长到大约 50GB 的数据库，我们想从该数据库中提取一组特定的表（大约 20 个）并将它们移动到一个新数据库中。所有这些都将在同一个 SQL Server 上。我们要拉出的表大约有 12GB 的空间（6GB 数据，6GB 索引）。

How can we move the tables from one database to the second but make sure the tables that are created in the new database are an exact copy of the originals (indexes, keys, etc.)? Ideally I want a copy/paste from within SQL Server Management Studio but I know this does not exist, so what are my options?

我们如何将表从一个数据库移动到第二个数据库，同时确保在新数据库中创建的表是原始数据库（索引、键等）的精确副本？理想情况下，我想要从 SQL Server Management Studio 中复制/粘贴，但我知道这不存在，那么我的选择是什么？

采纳答案by Joe Stefanelli

If you're moving the tables to a whole new database just because of growth, you might be better off considering using filegroupsin your existing database instead. There will be a lot fewer headaches going forward than trying to deal with two separate databases.

如果您只是因为增长而将表移动到一个全新的数据库，那么您最好考虑在现有数据库中使用文件组。与尝试处理两个独立的数据库相比，未来的麻烦会少得多。

EDIT

编辑

As I mentioned in my comments below, if you truly need a new database, depending on the total number of tables involved, it might be easier to restore a backup of the database under the new name and drop the tables you don't want.

正如我在下面的评论中提到的，如果您确实需要一个新数据库，根据所涉及的表总数，以新名称恢复数据库备份并删除不需要的表可能会更容易。

回答by dallin

To do this really easily with SQL Server 2008 Management Studio:

要使用 SQL Server 2008 Management Studio 轻松完成此操作：

1.) Right click on the database (not the table) and select Tasks -> Generate Scripts

1.) 右键单击​​数据库（不是表）并选择任务 -> 生成脚本

2.) Click Next on the first page

2.) 在第一页点击下一步

3.) If you want to copy the whole database, just click next. If you want to copy specific tables, click on "Select Specific Database Objects", select the tables you want, and then click next.

3.) 如果要复制整个数据库，只需单击下一步。如果要复制特定表，请单击“选择特定数据库对象”，选择所需的表，然后单击下一步。

4.) Select "Save to Clipboard" or "Save to File". IMPORTANT: Click the Advanced button next to "Save to File", find "Types of data to script", and change "Schema only" to "Schema and data" (if you want to create the table) or "Data only" (if you're copying data to an existing table). This is also where you'd set other options such as exactly what keys to copy, etc.

4.) 选择“保存到剪贴板”或“保存到文件”。重要提示：单击“保存到文件”旁边的“高级”按钮，找到“要脚本的数据类型”，然后将“仅架构”更改为“架构和数据”（如果要创建表）或“仅数据”（如果您将数据复制到现有表中）。这也是您设置其他选项的地方，例如要复制的确切键等。

5.) Click through the rest and you're done!

5.) 点击其余部分，你就完成了！

回答by Jeff Widmer

I did also find this potential solution using SQL Server Management Studio. You can generate the scripts for the specific tables to move and then export the data using the Generate Scripts Wizard and Import/Export Wizard in SQL Server Management Studio. Then on the new database you would run the scripts to create all of the objects and then import the data. We are probably going to go with the backup/restore method as described in @Joe Stefanelli's answer but I did find this method and wanted to post it for others to see.

我也使用 SQL Server Management Studio 找到了这个潜在的解决方案。您可以为要移动的特定表生成脚本，然后使用 SQL Server Management Studio 中的生成脚本向导和导入/导出向导导出数据。然后在新数据库上运行脚本以创建所有对象，然后导入数据。我们可能会采用@Joe Stefanelli 的回答中描述的备份/恢复方法，但我确实找到了这种方法，并希望将其发布给其他人查看。

To generate the sql script for the objects:

为对象生成 sql 脚本：

• SQL Server Management Studio > Databases > Database1 > Tasks > Generate Scripts...
• The SQL Server Scripts Wizard will start and you can choose the objects and settings to export into scripts
  • By default the scripting of Indexes and Triggers are not included so make sure to trun these on (and any others that you are interested in).

• SQL Server Management Studio > 数据库 > Database1 > 任务 > 生成脚本...
• SQL Server 脚本向导将启动，您可以选择要导出到脚本中的对象和设置
  • 默认情况下，索引和触发器的脚本不包括在内，因此请确保打开它们（以及您感兴趣的任何其他内容）。

To export the data from the tables:

要从表中导出数据：

• SQL Server Management Studio > Databases > Database1 > Tasks > Export Data...
• Choose the source and destination databases
• Select the tables to export
  • Make sure to check the Identity Insert checkbox for each table so that new identities are not created.

• SQL Server Management Studio > 数据库 > Database1 > 任务 > 导出数据...
• 选择源数据库和目标数据库
• 选择要导出的表
  • 确保选中每个表的标识插入复选框，以免创建新标识。

Then create the new database, run the scripts to create all of the objects, and then import the data.

然后创建新数据库，运行脚本以创建所有对象，然后导入数据。

回答by blorkfish

Try DBSourceTools.
http://dbsourcetools.codeplex.com.
This toolset uses SMO to script tables and data to disk, and also allows you to select which tables / views / Stored procedures to include.
When using a "deployment target", it will also automatically handle dependencies.
I have used it repeatedly for exactly this type of problem, and it's extremely simple and fast.

试试 DBSourceTools。
http://dbsourcetools.codeplex.com。
此工具集使用 SMO 将表和数据脚本写入磁盘，还允许您选择要包含的表/视图/存储过程。
当使用“部署目标”时，它也会自动处理依赖关系。
我已经反复使用它来解决这类问题，而且它非常简单和快速。

回答by chorbs

A lazy, efficient way to do this in T-SQL:

在 T-SQL 中执行此操作的一种懒惰、有效的方法：

In my case, some of the tables are large, so scripting out the data is impractical.

就我而言，有些表很大，因此编写数据脚本是不切实际的。

Also, we needed to migrate just a fraction of an otherwise very large database, so I didn't want to do backup / restore.

此外，我们只需要迁移一个非常大的数据库的一小部分，所以我不想做备份/恢复。

So I went with INSERT INTO / SELECT FROM and used information_schema etc to generate the code.

所以我使用 INSERT INTO / SELECT FROM 并使用 information_schema 等来生成代码。

Step 1: create your tables on new DB

第 1 步：在新数据库上创建表

For every table you want to migrate to new database, create that table on new database.

对于要迁移到新数据库的每个表，请在新数据库上创建该表。

Either script out the tables, or use SQL Compare, dynamic sql from information_schema -- many ways to do it. dallin's answer shows one way using SSMS (but be sure to select schema only).

要么编写表的脚本，要么使用 SQL 比较，来自 information_schema 的动态 sql——有很多方法可以做到。dallin 的回答显示了使用 SSMS 的一种方式（但一定要仅选择架构）。

Step 2: create UDF on target DB to produce column list

第 2 步：在目标 DB 上创建 UDF 以生成列列表

This is just a helper function used in generation of code.

这只是用于生成代码的辅助函数。

USE [staging_edw]
GO

CREATE FUNCTION dbo.udf_get_column_list
    (
        @table_name varchar(8000)
    )
RETURNS VARCHAR(8000)
AS
BEGIN

    DECLARE @var VARCHAR(8000)
    SELECT 
        @var = COALESCE(@var + ',', '', '') + c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.columns c 
    WHERE c.TABLE_SCHEMA + '.' + c.TABLE_NAME = @table_name
    AND c.COLUMN_NAME NOT LIKE '%hash%'

    RETURN @var
END

Step 3: create log table

第三步：创建日志表

The generated code will log progress into this table so you can monitor. But you have to create this log table first.

生成的代码会将进度记录到此表中，以便您进行监控。但是你必须先创建这个日志表。

USE staging_edw
GO

IF OBJECT_ID('dbo.tmp_sedw_migration_log') IS NULL 
CREATE TABLE dbo.tmp_sedw_migration_log 
    (
        step_number INT IDENTITY, 
        step VARCHAR(100),
        start_time DATETIME
    )

Step 4: generate migration script

第四步：生成迁移脚本

Here you generate the T-SQL that will migrate the data for you. It just generates INSERT INTO / SELECT FROM statements for every table, and logs its progress along the way.

在此生成将为您迁移数据的 T-SQL。它只是为每个表生成 INSERT INTO / SELECT FROM 语句，并在此过程中记录其进度。

This script does not actually modify anything. It just outputs some code, which you can inspect before executing.

这个脚本实际上并没有修改任何东西。它只是输出一些代码，您可以在执行前检查这些代码。

USE staging_edw
GO

-- newline characters for formatting of generated code
DECLARE @n VARCHAR(100) = CHAR(13)+CHAR(10)
DECLARE @t VARCHAR(100) = CHAR(9)
DECLARE @2n VARCHAR(100) = @n + @n
DECLARE @2nt VARCHAR(100) = @n + @n + @t
DECLARE @nt VARCHAR(100) = @n + @t
DECLARE @n2t VARCHAR(100) = @n + @t + @t
DECLARE @2n2t VARCHAR(100) = @n + @n + @t + @t
DECLARE @3n VARCHAR(100) = @n + @n + @n

-- identify tables with identity columns
IF OBJECT_ID('tempdb..#identities') IS NOT NULL 
DROP TABLE #identities;
SELECT 
    table_schema = s.name,
    table_name = o.name
INTO #identities
FROM sys.objects o 
JOIN sys.columns c on o.object_id = c.object_id
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE 1=1
AND c.is_identity = 1

-- generate the code
SELECT 
    @3n + '-- ' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME,
    @n + 'BEGIN TRY',
    @2nt + IIF(i.table_schema IS NOT NULL, 'SET IDENTITY_INSERT staging_edw.' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME + ' ON ', ''),
    @2nt + 'TRUNCATE TABLE staging_edw.' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME,
    @2nt + 'INSERT INTO staging_edw.' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME + ' WITH (TABLOCKX) ( ' + f.f + '  ) ',
    @2nt + 'SELECT ' + f.f + + @nt + 'FROM staging.' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME,
    @2nt + IIF(i.table_schema IS NOT NULL, 'SET IDENTITY_INSERT staging_edw.' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME + ' OFF ', ''),
    @2nt + 'INSERT INTO dbo.tmp_sedw_migration_log ( step, start_time ) VALUES  ( ''' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME + ' inserted successfully'', GETDATE() );' ,
    @2n + 'END TRY',
    @2n + 'BEGIN CATCH',
    @2nt + 'INSERT INTO dbo.tmp_sedw_migration_log ( step, start_time ) VALUES  ( ''' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME + ' FAILED'', GETDATE() );' ,
    @2n + 'END CATCH'
FROM INFORMATION_SCHEMA.tables t
OUTER APPLY (SELECT f = staging_edw.dbo.udf_get_column_list(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)) f
LEFT JOIN #identities i ON i.table_name = t.TABLE_NAME 
    AND i.table_schema = t.TABLE_SCHEMA
WHERE t.TABLE_TYPE = 'base table'

Step 5: run the code

第五步：运行代码

Now you just copy the output from step 4, paste into new query window, and run.

现在您只需复制第 4 步的输出，粘贴到新的查询窗口中，然后运行。

Notes

笔记

• In step 1, I exclude hash columns from the column list (in the UDF) because those are computed columns in my situation

• 在第 1 步中，我从列列表（在 UDF 中）中排除了哈希列，因为在我的情况下这些列是计算列

回答by ajdams

If you like/have SSIS you can explore using the Copy SQL Objects Task component to do this.

如果您喜欢/拥有 SSIS，您可以探索使用 Copy SQL Objects Task 组件来执行此操作。

回答by Badkid

SELECT *
INTO new_table_name [IN new database]
FROM old_tablename