我的部落格

MS Sqlserver 将表中数据按组分批导出Excel

先看效果：完整代码：

--功能：将表中数据分批导出到Excel
--分批要求：同组(相同组编号，要看实际业务)的不能被拆开到两个或多个表，即 同组的必须在同一份Excel。
--辅助表：BOM_BU6_成品料号顺序_210918。对各组(唯一值)设置顺序，对应要导出的顺序，排在前面的先导出。
--目标表：BOM_BU6_成品导入版本_210918。想要要导出table
--参考：https://stackoverflow.com/questions/9086880/t-sql-export-to-new-excel-file

--要先启用xp_cmdshell，否则会报错
--SQL Server 阻止了对组件 'xp_cmdshell' 的 过程 'sys.xp_cmdshell' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'xp_cmdshell'。有关启用 'xp_cmdshell' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。
sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go

DECLARE @start INT = 0,
	@cnt INT = 50;
DECLARE @end INT = 50;
DECLARE @db_name VARCHAR(100) = 'test',
	@table_name0 VARCHAR(100) = 'BOM_BU6_成品导入版本_210918',
	--@table_name varchar(100) = '[BOM_BU6_成品导入版本_210918]',   
	@file_dir VARCHAR(100) = 'z:\tem\xxx\',
	@file_name VARCHAR(100) = '',
	@file_part INT;

DECLARE @strSql NVARCHAR(4000);
--根据指定表名，生成要要导出数据的标题行。
DECLARE @columns VARCHAR(8000),
	@sql VARCHAR(8000),
	@data_file VARCHAR(100)

SELECT @columns = coalesce(@columns + ',', '') + column_name + ' as ' + column_name
FROM information_schema.columns
WHERE table_name = @table_name0 --'BOM_BU6_成品导入版本_210918'--@table_name

SELECT @columns = '''''' + replace(replace(@columns, ' as ', ''''' as '), ',', ',''''')

SET @file_part = 1;

WHILE @start < 137
BEGIN
	SET @strSql = ' SELECT * FROM ( SELECT TOP 100000 ' + 'A.*' + --在bpc命令中，字段 不能使用as，否则查询结果会错位，当前列的值显示成as的值。并且如果是NULL指，BPC导出时会自动处理成空白 而不是NULL。
		'FROM TEST..[BOM_BU6_成品导入版本_210918] AS A				' + 
		' LEFT JOIN TEST..[BOM_BU6_成品料号顺序_210918] AS B ON A.物料 = B.料号	' + --在bpc命令中不能直接使用表名，会提表名无效，加上【数据库名..】才可以。
		' WHERE B.[顺序] BETWEEN ' + convert(NVARCHAR(5), @start) + ' AND ' + convert(NVARCHAR(5), @start + @cnt) + --按指定的顺序从目标表，配合辅助表 取出数据
		' ORDER BY B.[顺序],A.[项目编号] ) x ';--子查询如果想用order 需要使用TOP、OFFSET 或 FOR XML配合。用order目的是将同组在一起而不是不按顺序的交叉显示.
	SET @file_name = @file_dir + 'Part_' + convert(NVARCHAR(2), @file_part) + '.xls';--xlsx还不行

	--创建含有要导出的数据文件
	SELECT @data_file = substring(@file_name, 1, len(@file_name) - charindex('\', reverse(@file_name))) + '\data_file.xls'

	----追加标题行到指定的Excel文件
	----文件名全称，不是路径，否则只有目录没有文件会错误的提示需要账号、密码或-T。如果是使用windows认证用-T，如果是sqlserver身份认证，账号密码用双引号引起来，-U "账号" -P "密码"
	SET @sql = 'exec master..xp_cmdshell ''bcp " select * from (select ' + @columns + ') as t" queryout "' + @file_name + '" -c -T''' --文件名全称，不是路径，否则只有目录没有文件会错误的提示需要账号、密码或-T

	EXEC (@sql)

	--先将数据加入到 数据文件 
	SET @sql = 'exec master..xp_cmdshell ''bcp " SELECT  * FROM ( ' + @strSql + ') AS T" queryout "' + @data_file + '" -m 999999 -c -T'''

	--set @sql='exec master..xp_cmdshell ''bcp " select '+@columns+' UNION ALL select top 10 * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -U "sa" -P "passX"'''
	EXEC (@sql)

	--复制数据文件数据到到要导出的 Excel 
	SET @sql = 'exec master..xp_cmdshell ''type ' + @data_file + ' >> "' + @file_name + '"'''

	EXEC (@sql)

	--数据导出完成，删除临时数据文件
	SET @sql = 'exec master..xp_cmdshell ''del ' + @data_file + ''''

	EXEC (@sql)

	--SET @end = @start + @cnt;
	SET @start = @start + @cnt + 1;--下一份导出的顺序编号范围
	SET @file_part = @file_part + 1;--下一个文件的编号。
END

--功能：将表中数据分批导出到Excel

--分批要求：同组(相同组编号，要看实际业务)的不能被拆开到两个或多个表，即同组的必须在同一份Excel。

--辅助表：BOM_BU6_成品料号顺序_210918。对各组(唯一值)设置顺序，对应要导出的顺序，排在前面的先导出。

--目标表：BOM_BU6_成品导入版本_210918。想要要导出table

--参考：https://stackoverflow.com/questions/9086880/t-sql-export-to-new-excel-file

--要先启用xp_cmdshell，否则会报错

--SQL Server 阻止了对组件 'xp_cmdshell' 的过程 'sys.xp_cmdshell' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'xp_cmdshell'。有关启用 'xp_cmdshell' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。

sp_configure 'show advanced options',1

reconfigure

sp_configure 'xp_cmdshell',1

reconfigure

DECLARE @start INT = 0,

@cnt INT = 50;

DECLARE @end INT = 50;

DECLARE @db_name VARCHAR(100) = 'test',

@table_name0 VARCHAR(100) = 'BOM_BU6_成品导入版本_210918',

--@table_name varchar(100) = '[BOM_BU6_成品导入版本_210918]',

@file_dir VARCHAR(100) = 'z:\tem\xxx\',

@file_name VARCHAR(100) = '',

@file_part INT;

DECLARE @strSql NVARCHAR(4000);

--根据指定表名，生成要要导出数据的标题行。

DECLARE @columns VARCHAR(8000),

@sql VARCHAR(8000),

@data_file VARCHAR(100)

SELECT @columns = coalesce(@columns + ',', '') + column_name + ' as ' + column_name

FROM information_schema.columns

WHERE table_name = @table_name0 --'BOM_BU6_成品导入版本_210918'--@table_name

SELECT @columns = '''''' + replace(replace(@columns, ' as ', ''''' as '), ',', ',''''')

SET @file_part = 1;

WHILE @start < 137

BEGIN

SET @strSql = ' SELECT * FROM ( SELECT TOP 100000 ' + 'A.*' + --在bpc命令中，字段不能使用as，否则查询结果会错位，当前列的值显示成as的值。并且如果是NULL指，BPC导出时会自动处理成空白而不是NULL。

'FROM TEST..[BOM_BU6_成品导入版本_210918] AS A ' +

' LEFT JOIN TEST..[BOM_BU6_成品料号顺序_210918] AS B ON A.物料 = B.料号 ' + --在bpc命令中不能直接使用表名，会提表名无效，加上【数据库名..】才可以。

' WHERE B.[顺序] BETWEEN ' + convert(NVARCHAR(5), @start) + ' AND ' + convert(NVARCHAR(5), @start + @cnt) + --按指定的顺序从目标表，配合辅助表取出数据

' ORDER BY B.[顺序],A.[项目编号] ) x ';--子查询如果想用order 需要使用TOP、OFFSET 或 FOR XML配合。用order目的是将同组在一起而不是不按顺序的交叉显示.

SET @file_name = @file_dir + 'Part_' + convert(NVARCHAR(2), @file_part) + '.xls';--xlsx还不行

--创建含有要导出的数据文件

SELECT @data_file = substring(@file_name, 1, len(@file_name) - charindex('\', reverse(@file_name))) + '\data_file.xls'

----追加标题行到指定的Excel文件

----文件名全称，不是路径，否则只有目录没有文件会错误的提示需要账号、密码或-T。如果是使用windows认证用-T，如果是sqlserver身份认证，账号密码用双引号引起来，-U "账号" -P "密码"

SET @sql = 'exec master..xp_cmdshell ''bcp " select * from (select ' + @columns + ') as t" queryout "' + @file_name + '" -c -T''' --文件名全称，不是路径，否则只有目录没有文件会错误的提示需要账号、密码或-T

EXEC (@sql)

--先将数据加入到数据文件

SET @sql = 'exec master..xp_cmdshell ''bcp " SELECT * FROM ( ' + @strSql + ') AS T" queryout "' + @data_file + '" -m 999999 -c -T'''

--set @sql='exec master..xp_cmdshell ''bcp " select '+@columns+' UNION ALL select top 10 * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -U "sa" -P "passX"'''

EXEC (@sql)

--复制数据文件数据到到要导出的 Excel

SET @sql = 'exec master..xp_cmdshell ''type ' + @data_file + ' >> "' + @file_name + '"'''

EXEC (@sql)

--数据导出完成，删除临时数据文件

SET @sql = 'exec master..xp_cmdshell ''del ' + @data_file + ''''

EXEC (@sql)

--SET @end = @start + @cnt;

SET @start = @start + @cnt + 1;--下一份导出的顺序编号范围

SET @file_part = @file_part + 1;--下一个文件的编号。

END

MS-Sql 2021年9月20日 7 浏览评论

USE OpenPLM
GO
SELECT file_id,name FROM sys.database_files;
GO
DBCC LOGINFO([OpenPLM])


USE [OpenPLM]
GO
ALTER DATABASE OpenPLM SET RECOVERY SIMPLE WITH NO_WAIT
GO
--简单模式
ALTER DATABASE OpenPLM SET RECOVERY SIMPLE 
GO
USE OpenPLM
GO
DBCC SHRINKFILE (N'InnovatorSolutions930_log' , 11, TRUNCATEONLY) 
GO
USE [OpenPLM]
GO
ALTER DATABASE OpenPLM SET RECOVERY FULL WITH NO_WAIT
GO
--还原为完全模式
ALTER DATABASE OpenPLM SET RECOVERY FULL 
GO

USE OpenPLM

SELECT file_id,name FROM sys.database_files;

DBCC LOGINFO([OpenPLM])

USE [OpenPLM]

ALTER DATABASE OpenPLM SET RECOVERY SIMPLE WITH NO_WAIT

--简单模式

ALTER DATABASE OpenPLM SET RECOVERY SIMPLE

USE OpenPLM

DBCC SHRINKFILE (N'InnovatorSolutions930_log' , 11, TRUNCATEONLY)

USE [OpenPLM]

ALTER DATABASE OpenPLM SET RECOVERY FULL WITH NO_WAIT

--还原为完全模式

ALTER DATABASE OpenPLM SET RECOVERY FULL

MS-Sql 2020年7月18日 353 浏览评论

MS Sqlserver 将表中数据按组分批导出Excel

IdentityServer 4 –js客户端_笔记

SAP 结构类型变量直接赋值字符串

.NET5 服务端已启用跨域配置客户端扔不能跨域获取数据

将网页/网站嵌入再SAP GUI主屏幕

SqlServer 数据库的事务日志已满，原因为“LOG_BACKUP”…

RFC6495 OAuth 2.0授权许可与授权流程

C#问号？运算符

XPath取XML标签attribute的值–fiddler应用

C# – 按指定项目数量分割数组，oracle in表达式参数支持最大上限1000个替代方案