我们如何将表格从一个数据库转移到另一个数据库,同时确保新数据库中创建的表格与原始表格完全相同(包括索引、键等)?理想情况下,我希望能够在SQL Server管理工具中进行复制和粘贴,但我知道这是不可能的,那么我有哪些选择?
1.) 右键单击数据库(而不是表),选择“任务”->“生成脚本”
2.) 在第一页上点击下一步
3.) 如果您想要复制整个数据库,只需点击下一步。如果要复制特定表,请单击“选择特定数据库对象”,选择所需的表,然后单击下一步。
4.) 选择“保存到剪贴板”或“保存到文件”。重要提示:在“保存到文件”旁边点击高级按钮,找到“脚本的数据类型”,并将“仅架构”更改为“架构和数据”(如果要创建表)或“仅数据”(如果要复制数据到现有表中)。这也是您可以设置其他选项,例如要复制哪些键等的地方。
5.) 点击其余部分,您就完成了!
如果只是因为数据库变大而将表格移动到一个全新的数据库中,您最好考虑在现有的数据库中使用文件组。与尝试处理两个独立的数据库相比,未来会更加轻松。
编辑
正如我在下面的评论中提到的那样,如果您真的需要一个新的数据库,根据所涉及的表格总数,恢复数据库的备份并删除不需要的表格可能会更容易。
SELECT *
INTO new_table_name [IN new database]
FROM old_tablename
试试DBSourceTools。
http://dbsourcetools.codeplex.com。
这个工具集使用SMO将表和数据脚本化到磁盘,并允许您选择要包括哪些表/视图/存储过程。
在使用“部署目标”时,它还会自动处理依赖项。
我已经多次用它来解决这种问题,而且非常简单快捷。
在我的情况下,一些表很大,因此脚本化数据是不切实际的。
另外,我们只需要迁移一个非常大的数据库的一小部分,所以我不想备份/恢复。
所以我选择了INSERT INTO / SELECT FROM,并使用information_schema等来生成代码。
对于要迁移到新数据库的每个表,在新数据库上创建该表。
可以脚本化表,或使用SQL Compare、information_schema的动态sql等多种方式。dallin的答案展示了一种使用SSMS的方法(但一定要仅选择模式)。
这只是用于生成代码的辅助函数。
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
生成的代码将记录进度到此表中,以便您进行监控。但是您必须先创建此日志表。
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
)
在这里,您将生成用于迁移数据的T-SQL。它只是为每个表生成INSERT INTO / SELECT FROM语句,并记录其进度。
此脚本实际上不会修改任何内容。它只输出一些代码,您可以在执行之前进行检查。
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'
现在,您只需要将步骤4中的输出复制并粘贴到新的查询窗口中,然后运行即可。