SQL Server存储过程：按条件批量生成INSERT语句
173 浏览量
更新于2024-08-31
1
收藏 397KB PDF 举报
本文档重点讨论了如何在SQL Server环境下创建一个增强版的存储过程，用于批量生成针对特定查询条件的INSERT语句，以高效地迁移数据到另一个具有相同结构的数据库表。通常，SQL Server本身并不直接支持根据查询条件自动生成这样的INSERT语句，因此开发者需要利用自定义脚本来实现这一功能。
存储过程名为"InsertGenerator"，其接受两个参数：@tableNameNVARCHAR(MAX)用于指定要导出数据的表名，@whereClauseNVARCHAR(MAX)则允许用户设置筛选条件，只导出满足该条件的数据。该存储过程利用了SQL Server的信息_schema系统视图，通过游标遍历表的列信息，包括列名和数据类型，从而构建INSERT和VALUES子句。
首先，它声明了三个变量：@string NVARCHAR(MAX)用于存储INSERT语句的前半部分，即基本的INSERT语句结构；@stringData NVARCHAR(MAX)用来存储VALUES部分，即实际的数据值；@dataType NVARCHAR(MAX)则用于存储对应列的数据类型信息。
存储过程的主要流程包括：
1. 使用信息_schema.columns获取表的列元数据。
2. 遍历这些列，构造INSERT语句的格式，如`INSERT INTO <table_name> (column1, column2, ...) VALUES (?, ?, ...)`。
3. 对于每行查询结果，根据数据类型将其转换为适当的值形式，并添加到VALUES部分。
4. 如果设置了筛选条件，只有满足条件的数据才会被插入到新表中，从而避免了全表扫描带来的性能问题。
通过这个存储过程，开发者可以更加灵活地处理大规模数据迁移，提高效率并减少不必要的资源消耗。此外，由于是自定义存储过程，可以根据实际需求进行扩展，比如添加错误处理、日志记录等功能，以满足不同场景下的数据导入需求。
这篇文档提供了一个实用的解决方案，帮助SQL Server用户在没有现成工具的情况下，通过编写存储过程实现按条件批量生成INSERT语句，有效地管理数据迁移操作。
将表里的数据批量生成将表里的数据批量生成INSERT语句的存储过程语句的存储过程 增强版增强版
主要介绍了将表里的数据批量生成INSERT语句的存储过程 增强版的相关资料,需要的朋友可以参考下
有时候，我们需要将某个表里的数据全部或者根据查询条件导出来，迁移到另一个相同结构的库中
目前SQL Server里面是没有相关的工具根据查询条件来生成INSERT语句的，只有借助第三方工具（third party tools）
这种脚本网上也有很多，但是网上的脚本还是欠缺一些规范和功能，例如：我只想导出特定查询条件的数据，网上的脚本都是导出全表数据
如果表很大，对性能会有很大影响
这里有一个存储过程（适用于SQLServer2005 或以上版本）
-- Author: <桦仔>
-- Blog: <http://www.cnblogs.com/lyhabc/>
-- Create date: <//>
-- Description: <根据查询条件导出表数据的insert脚本>
-- =============================================
CREATE PROCEDURE InsertGenerator
(
@tableName NVARCHAR(MAX),
@whereClause NVARCHAR(MAX)
)
AS
--Then it includes a cursor to fetch column specific information (column name and the data type thereof)
--from information_schema.columns pseudo entity and loop through for building the INSERT and VALUES clauses
--of an INSERT DML statement.
DECLARE @string NVARCHAR(MAX) --for storing the first half of INSERT statement
DECLARE @stringData NVARCHAR(MAX) --for storing the data (VALUES) related statement
DECLARE @dataType NVARCHAR(MAX) --data types returned for respective columns
DECLARE @schemaName NVARCHAR(MAX) --schema name returned from sys.schemas
DECLARE @schemaNameCount int--shema count
DECLARE @QueryString NVARCHAR(MAX) -- provide for the whole query,
set @QueryString=' '
--如果有多个schema，选择其中一个schema
SELECT @schemaNameCount=COUNT(*)
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @tableName
WHILE(@schemaNameCount>)
BEGIN
--如果有多个schema，依次指定
select @schemaName = name
from
(
SELECT ROW_NUMBER() over(order by s.schema_id) RowID,s.name
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @tableName
) as v
where RowID=@schemaNameCount
--Declare a cursor to retrieve column specific information
--for the specified table
DECLARE cursCol CURSOR FAST_FORWARD
FOR
SELECT column_name ,
data_type
FROM information_schema.columns
WHERE table_name = @tableName
AND table_schema = @schemaName
OPEN cursCol
SET @string = 'INSERT INTO [' + @schemaName + '].[' + @tableName + ']('
SET @stringData = ''
DECLARE @colName NVARCHAR()
FETCH NEXT FROM cursCol INTO @colName, @dataType
PRINT @schemaName
PRINT @colName
IF @@fetch_status <>
BEGIN
PRINT 'Table ' + @tableName + ' not found, processing skipped.'
CLOSE curscol
DEALLOCATE curscol
RETURN
END
WHILE @@FETCH_STATUS =
BEGIN
IF @dataType IN ( 'varchar', 'char', 'nchar', 'nvarchar' )
BEGIN
SET @stringData = @stringData + '''''''''+
isnull(' + @colName + ','''')+'''''',''+'
END
ELSE
IF @dataType IN ( 'text', 'ntext' ) --if the datatype
--is text or something else
BEGIN
SET @stringData = @stringData + '''''''''+
isnull(cast(' + @colName + ' as nvarchar(max)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted
--from varchar implicitly
BEGIN
SET @stringData = @stringData
+ '''convert(money,''''''+
isnull(cast(' + @colName
+ ' as nvarchar(max)),''.'')+''''''),''+'
END
ELSE
下载后可阅读完整内容，剩余3页未读，立即下载
2020-12-14 上传
105 浏览量
2020-12-15 上传
2024-05-14 上传
2012-12-19 上传
2023-03-31 上传
2023-03-31 上传
weixin_38709379
- 粉丝: 3
- 资源: 954
我的内容管理
展开
最新资源
- 正整数数组验证库：确保值符合正整数规则
- 系统移植工具集：镜像、工具链及其他必备软件包
- 掌握JavaScript加密技术：客户端加密核心要点
- AWS环境下Java应用的构建与优化指南
- Grav插件动态调整上传图像大小提高性能
- InversifyJS示例应用：演示OOP与依赖注入
- Laravel与Workerman构建PHP WebSocket即时通讯解决方案
- 前端开发利器：SPRjs快速粘合JavaScript文件脚本
- Windows平台RNNoise演示及编译方法说明
- GitHub Action实现站点自动化部署到网格环境
- Delphi实现磁盘容量检测与柱状图展示
- 亲测可用的简易微信抽奖小程序源码分享
- 如何利用JD抢单助手提升秒杀成功率
- 快速部署WordPress：使用Docker和generator-docker-wordpress
- 探索多功能计算器：日志记录与数据转换能力
- WearableSensing: 使用Java连接Zephyr Bioharness数据到服务器
