CODEWITHSUNDEEP
sqlsql-servert-sql
Owain Esau
Owain Esau

Reputation: 1922

TSQL - Create table columns based on records in a column

Im trying to automate the process of bulk importing CSV files into 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

The @sql bulk insert gets the column headers as required, but i cant figure out how to actually pass this into the CREATE TABLE statement.

Any help would be much appreciated!

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

After changing CREATE TABLE to use dynamic SQL i end up with the following (i also put it into 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 issue is, 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 the rowterminator is ',' It only picks up one column. If i change the RowTerminator to '\n' It puts it all in one line like:

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

Is there anyway i can set the LASTROW = the first instance of '\n'?

Upvotes: 4

Views: 691

Answers (2)

Hadi
Hadi

Reputation: 37313

You can use Dynamic SQL to achieve this:

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

String Split Function

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

You can try splitting the columns row using a split function instead of using rowterminator = ',' , you need to add one more step. Is to pass the comma seperated string (assign it to a variable first) you provided i your question, to the function above.

Function Reference

Upvotes: 2

Pream
Pream

Reputation: 537

First construct a string with the columns and the DataType and remove the last "," by using LEFT and LEN, use this string inside the dynamic query string to create the dynamic table that you want

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

Upvotes: 2

Related Questions