Upload CSV file in sql server and dynamically create table with n number of columns

--======================================================================
--Script To Import CSV File In Sql Server.
--======================================================================
DECLARE @ColumnCount INT,
@tableName Nvarchar(150),
@Counter INT,
@Sql Nvarchar(Max),
@Path Nvarchar(150),
@MaxColname NVARCHAR(MAX),
@Colname NVARCHAR(MAX),
@FIELDTERMINATOR NVARCHAR(5),
@FileOK  INT,
@FileOK1  INT


--======================================================================
 --Mandaterory Variable to Supply    
--======================================================================
-- @ColumnCount  :  Supply the total column count.
-- @tableName   :  The table name which you want to Create.
-- @Path    :  The path of you file in local machine.If you are supplying
-- a local path then run it on your local SQL server instance.
-- If you want to run it on server then,place your file on any folder of
-- your server and supply its path.
-- @FIELDTERMINATOR    : Supply the field Delemeter.For example '|'.
--========================================================================

SET @ColumnCount=10
SET @tableName='FIP_Document_DataDump'
SET @Path='C:\Puneet\Projects\LinkedIn\CSV\FIP - Document Data Dump.csv'
SET @FIELDTERMINATOR='|'
SET @Counter=1
SET @Sql=''

BEGIN TRY

--=========================== Error Log File ===============================--
-- If there comes an error while importing CSV file,the error related to CSV
-- file will be stored into file 'ImportErrorData.log.Error.Txt' and
-- 'ImportErrorData.log'.These files will create automatically in C drive,when
-- any error in CSV file will occur.
--==========================================================================--

 exec xp_fileExist 'c:\ImportErrorData.log.Error.Txt' ,@FileOK OUTPUT
    exec xp_fileExist 'c:\ImportErrorData.log' ,@FileOK1 OUTPUT

 IF(ISNULL(@FileOK,0)=1 AND ISNULL(@FileOK1,0)=1)
    BEGIN
  ---- To allow advanced options to be changed.

  EXEC sp_configure 'show advanced options', 1


  -- To update the currently configured value for advanced options.

  RECONFIGURE

  -- To enable the feature.
        print 'test'
  EXEC sp_configure 'xp_cmdshell', 1

  -- To update the currently configured value for this feature.

  RECONFIGURE

        EXEC XP_CMDSHELL 'del C:\ImportErrorData.log.Error.Txt'
  EXEC XP_CMDSHELL 'del C:\ImportErrorData.log'
    END



 IF EXISTS(SELECT 1 FROM SYS.TABLES WHERE name=@tableName)
 BEGIN
   EXEC('DROP TABLE '+@tableName)
 END


 WHILE (@Counter<=@ColumnCount)
 BEGIN
  IF(@Sql='')
   BEGIN
    SET @Sql='CREATE TABLE TempTable
       (
     
       Column'+Cast(@Counter as varchar(20))+' Nvarchar(MAX),'


   END
    ELSE
   BEGIN

     IF(@Counter<@ColumnCount)
      SET @Sql = @Sql + ' Column'+Cast(@Counter as varchar(20))+' Nvarchar(MAX),'
     ELSE
      SET @Sql = @Sql + ' Column'+Cast(@Counter as varchar(20))+' Nvarchar(MAX) )'
   END
 
  SET @Counter=@Counter+1
 END

 EXECUTE(@Sql)
    print @Sql

 SET @Sql='
 BULK INSERT TempTable
 FROM '''+@Path+'''
 WITH
 (
 FIRSTROW = 1,
 -- DATAFILETYPE = ''char'',
 -- MAXERRORS = 100,
 FIELDTERMINATOR = '''+@FIELDTERMINATOR+''',
    ERRORFILE = ''C:\ImportErrorData.log''
 --ROWTERMINATOR = ''^''
 );'

 print @Sql
 EXEC(@Sql)


 Select ROW_NUMBER() OVER(ORDER BY (select 1)) ID,*  INTO #temp from TempTable


 SELECT @MaxColname=
 STUFF((
 select ',ISNULL(MAX(LEN('+b.Colname+')),50) '+b.Colname from
 (select 1 ID,name as Colname from sys.columns  where object_name(Object_Id)='TempTable') b
 WHERE b.id=a.id
 FOR XML PATH('')), 1, 1, '')
 from (select 1 ID,name as Colname from sys.columns  where object_name(Object_Id)='TempTable') a
 group by a.ID

 --print @MaxColname
 --order by CAST(REPLACE(name,'Column','') AS INT)

 SET @Sql='
 select 1 ID,'+ @MaxColname +'
 INTO ##tempMax
 FROM #temp WHERE ID!=1'

 EXEC(@Sql)
-- print @Sql


 --select * from ##tempMax




 SELECT @Colname=
 STUFF((
 select ','+b.Colname from
 (select 1 ID,name as Colname from sys.columns  where object_name(Object_Id)='TempTable') b
 WHERE b.id=a.id
 FOR XML PATH('')), 1, 1, '')
 from (select 1 ID,name as Colname from sys.columns  where object_name(Object_Id)='TempTable') a
 group by a.ID



 set @Sql
   = 'select ROW_NUMBER() OVER(ORDER BY (select 1)) ID, [Value] Val1
   INTO ##temp1
   from #temp
   unpivot
   (
   value
   for Col in ('+ @Colname +')
   ) u
  WHERE ID=1 '

 EXEC(@Sql)


 --select * from ##temp1

 set @Sql
   = 'select ROW_NUMBER() OVER(ORDER BY (select 1)) ID, [Value] Val2
   INTO ##temp2
   from ##tempMax
   unpivot
   (
   value
   for Col in ('+ @Colname +')
   ) u
  WHERE ID=1 '
 EXEC(@Sql)

 --select * from ##temp2





 SELECT @Sql='CREATE TABLE '+@tableName+' ('+
 STUFF((
 select ','+b.Colname from
 (
  Select 1 as ID,'['+Val1+ '] NVARCHAR('+ CASE WHEN Val2>8000 THEN 'MAX' ELSE CAST(Val2 as char(5)) END +')' as Colname
  FROM ##temp1 a
  INNER JOIN ##temp2 b
  ON a.ID=b.ID
 ) b
 WHERE b.id=a.id
 FOR XML PATH('')), 1, 1, '')+')'
 from (
   Select 1 as ID,'['+Val1+ '] NVARCHAR('+ CASE WHEN Val2>8000 THEN 'MAX' ELSE CAST(Val2 as char(5)) END +')' as Colname
   FROM ##temp1 a
   INNER JOIN ##temp2 b
   ON a.ID=b.ID
   ) a
 group by a.ID


 EXEC(@Sql)
-- print @Sql

 SET @Sql='INSERT INTO '+@tableName+'
     Select '+ REPLACE(REPLACE(REPLACE(REPLACE(@MaxColname,'MAX(LEN(',''),'))',' as '),'ISNULL(',''),',50)','')+' FROM #temp WHERE ID!=1'

 EXEC(@Sql)
-- PRINT @Sql
    EXEC('SELECT CAST(Count(1) as Nvarchar(max)) +'' Records Imported'' as '+@tableName+' FROM '+@tableName)

   
 
 DROP TABLE #temp
 DROP TABLE ##tempMax
 DROP TABLE ##temp1
 DROP TABLE ##temp2
 DROP TABLE TempTable
END TRY
BEGIN CATCH

   SELECT 'There was an error! ' + ERROR_MESSAGE()

   IF OBJECT_ID('tempdb..#temp') IS NOT NULL
   DROP TABLE #temp

   IF OBJECT_ID('tempdb..##tempMax') IS NOT NULL
   DROP TABLE ##tempMax

   IF OBJECT_ID('tempdb..##temp1') IS NOT NULL
   DROP TABLE ##temp1

   IF OBJECT_ID('tempdb..##temp2') IS NOT NULL
   DROP TABLE ##temp2

   IF OBJECT_ID('TempTable') IS NOT NULL
   DROP TABLE TempTable

END CATCH


--select * from Tmk_mdcTMMCL

--drop table Tmk_mdcTMMAC