sql - TSQL - 根据列中的记录创建表列

Question

我正在尝试自动化将 CSV 文件批量导入 sql-server 的过程，到目前为止我有这个:

----Allow for SQL to use cmd shell
--EXEC sp_configure 'show advanced options', 1    -- To allow advanced options to be changed.
--RECONFIGURE -- To update the currently configured value for advanced options.
--EXEC sp_configure 'xp_cmdshell', 1  -- To enable the feature.
--RECONFIGURE -- To update the currently configured value for this feature.

SET NOCOUNT ON
--Loop through all of the files
CREATE TABLE #tmp(excelFileName VARCHAR(100));
CREATE TABLE #tmpCol(col NVARCHAR(MAX));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /B "C:\Users\owain.esau\OneDrive\Work\Companies\IronSude\backupFiles\"';

DECLARE @fileName   varchar(100)
DECLARE @tableName  varchar(MAX)
DECLARE @sql        varchar(MAX)

While (Select Count(*) From #tmp where excelFileName is not null) > 0
Begin

    Select Top 1 @fileName = excelFileName From #tmp

    SET @sql = 'BULK INSERT #tmpCol FROM "C:\Users\owain.esau\OneDrive\Work\Companies\IronSide\backupFiles\' + @filename + '" WITH (ROWTERMINATOR = '','', LASTROW = 1)'
    EXEC (@sql)

    UPDATE #tmpCol SET col = REPLACE(col, '"', '')
    UPDATE #tmpCol SET col = REPLACE(col, ' ', '')

    SET @tableName = LEFT(@filename, LEN(@filename) -4)
    CREATE TABLE @tableNAme ( )


    Delete from #tmp Where excelFileName = @FileName

End
DROP TABLE #tmp
DROP TABLE #tmpCol

@sql 批量插入根据需要获取列标题，但我无法弄清楚如何将其实际传递到 CREATE TABLE 语句中。

任何帮助将不胜感激!

----- 编辑---------------------------------------- ----------

将 CREATE TABLE 更改为使用动态 SQL 后，我最终得到以下结果(我也将其放入循环中):

----Allow for SQL to use cmd shell
--EXEC sp_configure 'show advanced options', 1    -- To allow advanced options to be changed.
--RECONFIGURE -- To update the currently configured value for advanced options.
--EXEC sp_configure 'xp_cmdshell', 1  -- To enable the feature.
--RECONFIGURE -- To update the currently configured value for this feature.

SET NOCOUNT ON
--Loop through all of the files
CREATE TABLE #tmp(excelFileName VARCHAR(100));
CREATE TABLE #tmpCol(col NVARCHAR(MAX));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /B "C:\Users\owain.esau\OneDrive\Work\Companies\IronSude\backupFiles\"';

DECLARE @fileName   varchar(100)
DECLARE @tableName  varchar(MAX)
DECLARE @colName    varchar(MAX)
DECLARE @sql        varchar(MAX)
DECLARE @sql2       varchar(max)

While (Select Count(*) From #tmp where excelFileName is not null) > 0
Begin

    Select Top 1 @fileName = excelFileName From #tmp

    SET @sql = 'BULK INSERT #tmpCol FROM "C:\Users\owain.esau\OneDrive\Work\Companies\IronSude\backupFiles\' + @filename + '" WITH (ROWTERMINATOR = '','', LASTROW = 1)'
    EXEC (@sql)

    UPDATE #tmpCol SET col = REPLACE(col, '"', '')
    UPDATE #tmpCol SET col = REPLACE(col, ' ', '')

    WHILE (SELECT COUNT(*) FROM #tmpCol WHERE col IS NOT NULL) > 0
    BEGIN

        Select Top 1 @colName = col From #tmpCol

        SET @tableName = LEFT(@filename, LEN(@filename) - 5)


        SET @sql2 = 'CREATE TABLE [' + @tableName + ']('
        SELECT @sql2 = @sql2 + '[' + col + '],' FROM #tmpCol
        SET @sql2 = SUBSTRING(@sql2,1,LEN(@sql2) -1) + 'NVARCHAR(MAX)) '

        EXEC(@sql2)

        DELETE FROM #tmpCol WHERE col = @colName

    END

    Delete from #tmp Where excelFileName = @FileName

End

DROP TABLE #tmp
DROP TABLE #tmpCol

问题是，第一个动态 SQL 查询:

 SET @sql = 'BULK INSERT #tmpCol FROM "C:\Users\owain.esau\OneDrive\Work\Companies\IronSude\backupFiles\' + @filename + '" WITH (ROWTERMINATOR = '','', LASTROW = 1)'

由于最后一行设置为 1 并且行终止符为“,”，因此它只选取一列。如果我将 RowTerminator 更改为 '\n' ，它会将其全部放在一行中，例如:

"Attachment Id","Attachment Owner Id","Modified By","Created By","Created Time","Modified Time","File Name",Size,"Parent Id","Attachment Type",Documents

我是否可以设置 LASTROW = '\n' 的第一个实例？

Answer 1

您可以使用动态 SQL 来实现此目的:

SET @tableName = LEFT(@filename, LEN(@filename) -4)

DECLARE @strQuery VARCHAR(MAX)
SET @strQuery = 'CREATE TABLE [' + @tableName + ']('

SELECT @strQuery = @strQuery + '[' + col  + '],' FROM #tmpCol

SET @strQuery = SUBSTRING(@strQuery,1,LEN(@strQuery) -1) + ') '

--If you want to execute the create table query
EXEC(@strQuery)

Delete from #tmp Where excelFileName = @FileName

字符串分割函数

CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 
        'Col' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

您可以尝试使用 split 函数来拆分列行，而不是使用 rowterminator = ',' ，您需要再添加一个步骤。是将您在问题中提供的逗号分隔字符串(首先将其分配给变量)传递给上面的函数。

函数引用

• https://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html