SQL Server 动态生成数据库所有表Insert语句

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

一、 背景

  SQL Server,如果我们需要把数据库A的所有表数据到数据库B中,通常我们会怎么做呢?我会使用SSMS的导入导出功能,进行表数据的导入导出,无可厚非,这样的导入非常简单和方便;

  但是,当我们的表有上百个,而且有些表是有自增ID的,那么这个时候使用SSMS的话,你需要一个个手动设置(如图1),你要知道,需要设置上百个的这些选项是件多么痛苦的事情,而且最后很可能会因为外键约束导致导入导出失败。

wpsFA22.tmp

(图1)

  虽然SSMS在导入导出的最后一步提供了生成SSIS包的功能,但是对于转移数据的需求来说,还是无法达到我想要的快速、方便。

  自然而然,我想到了INSERT INTO XX SELECT FROM XX WHERE这样的方式(这种方式的好处就是可以对数据记录、字段进行控制),但是如何才能快速生成整个数据库所有表的这些语句呢?

  假如你需要批量生成下面的SQL,我想这篇文章就可以帮到你了:

复制代码
复制代码
--[OpinionList]
SET IDENTITY_INSERT [master_new].[dbo].[OpinionList] ON
INSERT INTO [master_new].[dbo].[OpinionList](Id,Batch,LinkId,DB_Names,CreateTime)
SELECT * FROM [DBA_DB].[dbo].[OpinionList]
SET IDENTITY_INSERT [master_new].[dbo].[OpinionList] OFF
GO
复制代码
复制代码

 

二、 脚本解释

(一) 我编写了一个模板,这个模板你只需要设置@fromdb和@todb的名称,这样就会生成从@fromdb导出所有表插入到@todb中的SQL语句,需要注意的是:要选择@fromdb对应的数据库执行模板SQL,不然无法生成需要的表和字段。

复制代码
复制代码
DECLARE @fromdb VARCHAR(100)
DECLARE @todb VARCHAR(100)
DECLARE @tablename VARCHAR(100)
DECLARE @columnnames NVARCHAR(300)
DECLARE @isidentity NVARCHAR(30)
DECLARE @temsql NVARCHAR(max)
DECLARE @sql NVARCHAR(max)
SET @fromdb = 'master'
SET @todb = 'master_new'

--游标
DECLARE @itemCur CURSOR
SET @itemCur = CURSOR FOR 
    SELECT '['+[name]+']' from sys.tables WHERE type='U' order by name

OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @tablename
WHILE @@FETCH_STATUS=0
BEGIN
    
    SET @sql = ''

    --获取表字段
    SET @temsql = N'
    BEGIN
    SET @columnnamesOUT =''''
    SELECT @columnnamesOUT = @columnnamesOUT + '','' + name 
    From sys.columns where object_id=OBJECT_ID(''['+@fromdb+'].dbo.'+@tablename+''')
    order by column_id
    SELECT @columnnamesOUT=substring(@columnnamesOUT,2,len(@columnnamesOUT))
    END
    '
    EXEC sp_executesql @temsql,N'@columnnamesOUT NVARCHAR(300) OUTPUT',@columnnamesOUT=@columnnames OUTPUT

    PRINT ('--'+@tablename)

    --判断是否有自增字段
    SET @temsql = N'
    BEGIN
    SET @isidentityOUT =''''
    SELECT @isidentityOUT = name 
    From sys.columns where object_id=OBJECT_ID(''['+@fromdb+'].dbo.'+@tablename+''')
    and is_identity = 1
    END
    '
    EXEC sp_executesql @temsql,N'@isidentityOUT NVARCHAR(30) OUTPUT',@isidentityOUT=@isidentity OUTPUT

    --IDENTITY_INSERT ON
    IF @isidentity != ''
    BEGIN
        SET @sql = 'SET IDENTITY_INSERT ['+@todb+'].[dbo].['+@tablename+'] ON
'
    END

    --INSERT
    SET @sql = @sql+'INSERT INTO ['+@todb+'].[dbo].['+@tablename+']('+@columnnames+')
SELECT * FROM ['+@fromdb+'].[dbo].['+@tablename+']'

    --IDENTITY_INSERT OFF
    IF @isidentity != ''
    BEGIN
        SET @sql = @sql+'
SET IDENTITY_INSERT ['+@todb+'].[dbo].['+@tablename+'] OFF'
    END

    --返回SQL
    PRINT(@sql)PRINT('GO')+CHAR(13)

    FETCH NEXT FROM @itemCur INTO @tablename
END 

CLOSE @itemCur
DEALLOCATE @itemCur
复制代码
复制代码

 

(二) 下面就是返回的生成的部分脚本,模板会自动判断表是否存在自增字段,如果存在就会生成对应的IDENTITY_INSERT语句。

复制代码
复制代码
--spt_values
INSERT INTO [master_new].[dbo].[spt_values](name,number,type,low,high,status)
SELECT * FROM [master].[dbo].[spt_values]
GO

--[OpinionList]
SET IDENTITY_INSERT [master_new].[dbo].[OpinionList] ON
INSERT INTO [master_new].[dbo].[OpinionList](Id,Batch,LinkId,DB_Names,CreateTime)
SELECT * FROM [DBA_DB].[dbo].[OpinionList]
SET IDENTITY_INSERT [master_new].[dbo].[OpinionList] OFF
GO
复制代码
复制代码
分类:  MSMQ, mysql

本文转自左正博客园博客,原文链接:http://www.cnblogs.com/soundcode/p/4816377.html ,如需转载请自行联系原作者
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
9天前
|
SQL 存储 数据管理
SQL Server数据库
SQL Server数据库
20 11
|
4天前
|
SQL 监控 关系型数据库
MySQL数据库中如何检查一条SQL语句是否被回滚
检查MySQL中的SQL语句是否被回滚需要综合使用日志分析、事务状态监控和事务控制语句。理解和应用这些工具和命令,可以有效地管理和验证数据库事务的执行情况,确保数据的一致性和系统的稳定性。此外,熟悉事务的ACID属性和正确设置事务隔离级别对于预防数据问题和解决事务冲突同样重要。
15 2
|
19天前
|
SQL XML Java
mybatis :sqlmapconfig.xml配置 ++++Mapper XML 文件(sql/insert/delete/update/select)(增删改查)用法
当然,这些仅是MyBatis功能的初步介绍。MyBatis还提供了高级特性,如动态SQL、类型处理器、插件等,可以进一步提供对数据库交互的强大支持和灵活性。希望上述内容对您理解MyBatis的基本操作有所帮助。在实际使用中,您可能还需要根据具体的业务要求调整和优化SQL语句和配置。
26 1
|
16天前
|
SQL 安全 数据库
基于SQL Server事务日志的数据库恢复技术及实战代码详解
基于事务日志的数据库恢复技术是SQL Server中一个非常强大的功能,它能够帮助数据库管理员在数据丢失或损坏的情况下,有效地恢复数据。通过定期备份数据库和事务日志,并在需要时按照正确的步骤恢复,可以最大限度地减少数据丢失的风险。需要注意的是,恢复数据是一个需要谨慎操作的过程,建议在执行恢复操作之前,详细了解相关的操作步骤和注意事项,以确保数据的安全和完整。
35 0
|
19天前
|
前端开发 C# 设计模式
“深度剖析WPF开发中的设计模式应用:以MVVM为核心,手把手教你重构代码结构,实现软件工程的最佳实践与高效协作”
【8月更文挑战第31天】设计模式是在软件工程中解决常见问题的成熟方案。在WPF开发中,合理应用如MVC、MVVM及工厂模式等能显著提升代码质量和可维护性。本文通过具体案例,详细解析了这些模式的实际应用,特别是MVVM模式如何通过分离UI逻辑与业务逻辑,实现视图与模型的松耦合,从而优化代码结构并提高开发效率。通过示例代码展示了从模型定义、视图模型管理到视图展示的全过程,帮助读者更好地理解并应用这些模式。
34 0
|
19天前
|
SQL 数据处理 数据库
|
19天前
|
SQL 存储 调度
|
19天前
|
SQL 安全 数据库
|
19天前
|
Java 数据库连接 数据库
告别繁琐 SQL!Hibernate 入门指南带你轻松玩转 ORM,解锁高效数据库操作新姿势
【8月更文挑战第31天】Hibernate 是一款流行的 Java 持久层框架,简化了对象关系映射(ORM)过程,使开发者能以面向对象的方式进行数据持久化操作而无需直接编写 SQL 语句。本文提供 Hibernate 入门指南,介绍核心概念及示例代码,涵盖依赖引入、配置文件设置、实体类定义、工具类构建及基本 CRUD 操作。通过学习,你将掌握使用 Hibernate 简化数据持久化的技巧,为实际项目应用打下基础。
40 0
|
19天前
|
SQL 存储 监控