MS-Sql

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 浏览评论

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

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 浏览评论

USE master; 
GO 
DECLARE @SQL VARCHAR(MAX); 
SET @SQL='' 
SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID) 
FROM master..sysprocesses 
WHERE dbid=DB_ID('数据库名'); 

EXEC(@SQL);
ALTER DATABASE 数据库名 SET MULTI_USER;

USE master;

DECLARE @SQL VARCHAR(MAX);

SET @SQL=''

SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID)

FROM master..sysprocesses

WHERE dbid=DB_ID('数据库名');

EXEC(@SQL);

ALTER DATABASE 数据库名 SET MULTI_USER;

MS-Sql 2019年11月8日 153 浏览评论

SQL Server 去除字段前导 0

1，sql查询：

--：替换全数字字段的前导0
SELECT  ID,
	LX_IMA01 as [RAW],
	SUBSTRING(LX_IMA01,(PATINDEX('%[^0]%',LX_IMA01)),LEN(LX_IMA01)) as [NEW] 
FROM PART 
WHERE CLASSIFICATION= 'SAP' and SUBSTRING(lx_ima01,1,1)='0' AND PATINDEX( '%[^0-9]%',LX_IMA01)=0 --数字判断

--：替换全数字字段的前导0

SELECT ID,

LX_IMA01 as [RAW],

SUBSTRING(LX_IMA01,(PATINDEX('%[^0]%',LX_IMA01)),LEN(LX_IMA01)) as [NEW]

FROM PART

WHERE CLASSIFICATION= 'SAP' and SUBSTRING(lx_ima01,1,1)='0' AND PATINDEX( '%[^0-9]%',LX_IMA01)=0 --数字判断

查询结果 2，执行更新 [crayon-615246350a5fc31760…

MS-Sql 2019年10月31日 207 浏览评论

MS SQL Server Join连接查询图例

LEFT JOIN vs. LEFT OUTER JOIN Join关系图解

MS-Sql 2019年8月22日 184 浏览评论

Sql 生成数字–取最小流水号实例

说明：innovator.PART表LX_IMA01字段值，长度13，最后一段为4位纯数字流水号。功能：获取最小且未在PART表LX_IMA01字段使用的流…

MS-Sql 2019年7月4日 219 浏览评论

我的部落格

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

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

SQL Server Profiler检视查询窗口执行过程

SqlServer 函数Declare通过传变量查询超慢解决办法

MS SqlServer恢复变成(单个用户)故障恢复

SQL Server 去除字段前导 0

MS SQL Server Join连接查询图例

Sql 生成数字–取最小流水号实例