Excel剪切板转SQL表

Excel转SQL表语句，首行是标题，（以第三列为判断标准）空白行不要，（针对合并单元格）检索到空白单元格复制粘贴上行同列单元格

ALTER function [dbo].[ExcelConvertToTableSQL]
(
	@ClipboardText nvarchar(max)
)   

returns nvarchar(max)   
as   
begin   
	
	-- 假设剪切板文本已经传送到变量 @ClipboardText 中
	--看看能不能在sql处理单引号还是在传送前就要处理，ok
	SET @ClipboardText = REPLACE (@ClipboardText, '''', '''''' )
	-- 将剪切板文本按行分割
	DECLARE @Rows TABLE (id int identity(1,1),RowText NVARCHAR(MAX));
	insert into @Rows(RowText) SELECT cast(trim(left(value,len(value)-1)) as NVARCHAR(255))
	FROM STRING_SPLIT(@ClipboardText, CHAR(10)) 
	WHERE TRIM(value) <> '' and CHARINDEX(CHAR(9),value,CHARINDEX(CHAR(9),value,CHARINDEX(CHAR(9),value)+1)+1) > 5
	--SELECT * FROM @Rows

	-- 处理第一行以创建列名
	DECLARE @ColumnNames NVARCHAR(MAX);
	SELECT TOP 1 @ColumnNames = RowText FROM @Rows;
	DECLARE @ColumnName NVARCHAR(255);

	-- 创建动态 SQL 语句
	DECLARE @SQL NVARCHAR(MAX) = 'DECLARE @DynamicTable TABLE (';
	DECLARE @Cols int=0--计算列数
	WHILE LEN(@ColumnNames) > 0
	BEGIN
		SET @ColumnName = LEFT(@ColumnNames, CHARINDEX(CHAR(9), @ColumnNames + CHAR(9)) - 1);
		SET @SQL = @SQL + QUOTENAME(@ColumnName) + ' NVARCHAR(255), ';
		SET @ColumnNames = STUFF(@ColumnNames, 1, LEN(@ColumnName) + 1, ''); -- 移除已处理的列名
		SET @Cols=@Cols+1
	END

	-- 移除最后的逗号和空格 -2不对？因为NVARCHAR？
	SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ');';

	-- 删除Rows表第一行以便后续使用
	DELETE FROM @Rows WHERE id=1;

	-- 执行动态 SQL 创建表变量
	--EXEC sp_executesql @SQL;


	DECLARE @Cells TABLE (id int identity(1,1),Cell NVARCHAR(255));
	-- 处理每一行数据
	DECLARE @CurrentRow NVARCHAR(MAX)
	DECLARE @CellValue NVARCHAR(255)
	DECLARE @rowid int=0
	DECLARE @cellid int=0
	DECLARE RowCursor CURSOR FOR SELECT id,RowText FROM @Rows;
	OPEN RowCursor;

	FETCH NEXT FROM RowCursor INTO @rowid,@CurrentRow

	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- 将单元格插入到动态表变量中
		SET @SQL = @SQL +'
		' + 'INSERT INTO @DynamicTable VALUES (';
	
		-- 按制表符分割单元格
		insert into @Cells SELECT cast(trim(value) as NVARCHAR(255))
			FROM STRING_SPLIT(@CurrentRow, CHAR(9)) 
		DECLARE CellCursor CURSOR FOR SELECT id,Cell FROM @Cells
		WHERE id BETWEEN 1+@Cols*(@rowid - 2) AND @Cols*(@rowid - 1);
		OPEN CellCursor;
		FETCH NEXT FROM CellCursor INTO @cellid,@CellValue;
		WHILE @@FETCH_STATUS = 0
		BEGIN
			-- 检查当前单元格是否为空
			IF @CellValue IS NULL OR @CellValue = ''
			BEGIN
				SELECT @CellValue=Cell FROM @Cells WHERE id= @cellid-@Cols
				UPDATE @Cells SET Cell=@CellValue WHERE id= @cellid
			END

			-- 将单元格值添加到插入语句中
			SET @SQL = @SQL + QUOTENAME(@CellValue, '''') + ', ';

			FETCH NEXT FROM CellCursor INTO @cellid,@CellValue;
		END
		CLOSE CellCursor;
		DEALLOCATE CellCursor;
		-- 移除最后的逗号和空格
		SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ');';

		-- 执行插入语句
		--EXEC sp_executesql @InsertSQL;

		FETCH NEXT FROM RowCursor INTO @rowid,@CurrentRow
	END

	CLOSE RowCursor;
	DEALLOCATE RowCursor;

	-- 最后选择动态表变量中的所有数据
	SET @SQL = @SQL +'
	' + 'SELECT * FROM @DynamicTable;';

	--SELECT @SQL
	--EXEC sp_executesql @SQL;

	RETURN @SQL
end