TSQL - create table columns based on records in a column

Question

I am trying to automate the process of bulk import of CSV files to sql server, so far I have this:

----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

Bulk insert @sqlgets column headers as needed, but I can't figure out how to pass this into the statement CREATE TABLE.

Any help would be greatly appreciated!

----- EDIT ----------------------------------------- --- -------

After changing the CREATE TABLE to use dynamic SQL, I end up with the following (I also put it in a loop):

----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

The problem is that this is the first Dynamic SQL query:

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

Since the last row is set to 1, and rowterminator is ",", it only picks up one column. If I changed RowTerminator to "\ n", it puts everything on one line, for example:

"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

Answer 2

DataType "," LEFT LEN, , ,

Declare @Columnslist varchar(max)

SELECT @Columnslist = select COLUMN_NAME+' NVARCHAR(MAX),' from TABLE_NAME for xml path('')

SELECT @Columnslist = LEFT(@Columnslist, LEN(@Columnslist) - 1)

Declare @create_cmd varchar(max)
set @create_cmd=
'If Object_id('+char(39)+@tableName+char(39)+') is Not NULL
Drop Table '+@tableName+' Create table '+@tableName+'('+@Columnslist+')'

EXEC(@create_cmd);