以 点击打开链接 这个贴子为例:
USE tempdb
GO
--1. 按行分割表值函数
IF OBJECT_ID('dbo.Fun_SplitByLine') IS NOT NULL
DROP FUNCTION dbo.Fun_SplitByLine
GO
-- =============================================
-- Author: yenange
-- Create date: 2017-04-26
-- Description: 按行分割表值函数
-- =============================================
CREATE FUNCTION dbo.Fun_SplitByLine
(
@str NVARCHAR(MAX)
)
RETURNS
@t TABLE
(
rowNum INT,
line NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @i INT
SET @i=1
WHILE CHARINDEX(CHAR(10),@str)>0
BEGIN
INSERT INTO @t(rowNum,line)
SELECT @i,SUBSTRING(@str,1,CHARINDEX(char(10),@str))
SET @str = SUBSTRING(@str,CHARINDEX(char(10),@str)+1,LEN(@str))
SET @i = @i+1
END
IF LEN(@str)>0
BEGIN
INSERT INTO @t(rowNum,line) VALUES(@i,@str)
END
UPDATE @t SET line=REPLACE(REPLACE(line,char(13),''),CHAR(10),'')
RETURN
END
GO
--2.
IF OBJECT_ID('[dbo].[Fun_SplitBySpace]') IS NOT NULL
DROP FUNCTION [dbo].Fun_SplitBySpace
GO
CREATE FUNCTION [dbo].Fun_SplitBySpace(@str NVARCHAR(MAX),@splitor CHAR(1))
RETURNS @table TABLE (rowNum INT IDENTITY(1,1), [item] NVARCHAR(max))
AS
BEGIN
DECLARE @tmp NVARCHAR(MAX),@single NVARCHAR(MAX),@idx INT
SET @idx=1;
WHILE @idx<=LEN(@str)
BEGIN
SET @tmp=SUBSTRING(@str,@idx,1)
--如果不是空格,制表符,传入参数,则累加
IF @tmp NOT IN ( ' ',N' ',CHAR(9),@splitor)
BEGIN
SET @single=ISNULL(@single,'')+@tmp;
END
--否则如果传入切分字符为空或当前字符=传入切分字符,则插入
ELSE IF LEN(@single)>0 AND (ISNULL(@splitor,'')='' OR (@splitor>'' AND @tmp=@splitor))
BEGIN
INSERT INTO @table(item)
SELECT @single
SET @single=''
END
SET @idx=@idx+1
END
IF @single>''
BEGIN
INSERT INTO @table(item)
SELECT @single
END
RETURN
END
GO
--SELECT *,LEN(item),UNICODE(item) FROM dbo.Fun_SplitBySpace(N'THPALL5 产成品编码 名称 晶元名称 母批号 批号ID 销售数量 产品库存 封装在制 晶元ID 采购未回 晶元库存 CP在制 划片未回'
--,'')
--3. 产生数据SQL
IF OBJECT_ID('dbo.Proc_CreateScript') IS NOT NULL
DROP PROC dbo.Proc_CreateScript
GO
-- =============================================
-- Author: yenange
-- Create date: 2018-11-09
-- Description: 根据文本,生成测试表和数据
-- =============================================
CREATE PROCEDURE dbo.Proc_CreateScript
@str NVARCHAR(MAX)=N'THPALL5 产成品编码 名称 晶元名称 母批号 批号ID 销售数量 产品库存 封装在制 晶元ID 采购未回 晶元库存 CP在制 划片未回
2231 C0101000675 123A 456 456 221747 80300 19700 40000 221686 4200000 2660000 NULL 28000
2232 C0101000676 4566 123 123 221759 435000 NULL NULL 221668 6955000 6955000 NULL NULL
2230 C0101000675 123A 123 123 221755 NULL 1070000 NULL 221668 6955000 6955000 NULL NULL' --数据字符串
,@header BIT=1 --第一行是否为表头
,@tableName NVARCHAR(MAX)=N't'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @t TABLE (
rowNum INT PRIMARY KEY,
line NVARCHAR(MAX)
)
INSERT INTO @t
SELECT rowNum,line FROM dbo.Fun_SplitByLine(@str)
DECLARE @i INT,@imax INT,@line NVARCHAR(MAX),@sql NVARCHAR(MAX),@cols NVARCHAR(MAX),@insertSQL NVARCHAR(MAX),@values NVARCHAR(MAX)
SELECT @i=1,@imax=MAX(rowNum) FROM @t
SET @sql =N'USE tempdb
GO
IF OBJECT_ID(''@tableName'') IS NOT NULL
DROP TABLE @tableName
GO
CREATE TABLE @tableName(
@cols
)
GO
SET NOCOUNT ON
@insertSQL
'
--
WHILE @i<=@imax
BEGIN
SELECT @line = line FROM @t WHERE rowNum=@i
IF @i=1
BEGIN
SELECT @cols=STUFF((
SELECT ';,['+CASE WHEN @header=1 then item else 'c'+ltrim(rowNum) END +'] NVARCHAR(MAX)'
FROM dbo.Fun_SplitBySpace(@line,'') FOR XML PATH('')
),1,2,'')
SET @cols=REPLACE(@cols,';','
')
END
IF (@i=1 AND @header=0) OR @i>1
BEGIN
SELECT @insertSQL=ISNULL(@insertSQL,'')+'INSERT INTO @tableName VALUES('+
STUFF((SELECT ',N'''+item+''''FROM dbo.Fun_SplitBySpace(@line,'') FOR XML PATH('')
),1,1,'')+');'
SET @insertSQL=REPLACE(@insertSQL,'N''NULL''','NULL')
SET @insertSQL=REPLACE(@insertSQL,';','
')
END
SET @i=@i+1
END
--replace
SET @sql=REPLACE(@sql,'@cols',@cols)
SET @sql=REPLACE(@sql,'@insertSQL',@insertSQL)
SET @sql=REPLACE(@sql,'@tableName','dbo.['+@tableName+']')
PRINT @sql;
END
GO
EXEC dbo.Proc_CreateScript
@str =N'THPALL5 产成品编码 名称 晶元名称 母批号 批号ID 销售数量 产品库存 封装在制 晶元ID 采购未回 晶元库存 CP在制 划片未回
2231 C0101000675 123A 456 456 221747 80300 19700 40000 221686 4200000 2660000 NULL 28000
2232 C0101000676 4566 123 123 221759 435000 NULL NULL 221668 6955000 6955000 NULL NULL
2230 C0101000675 123A 123 123 221755 NULL 1070000 NULL 221668 6955000 6955000 NULL NULL' --数据字符串
,@header =1 --第一行是否为表头
,@tableName =N't'