Bulk Copy/Insert from a CSV file to SQL Directly

Introduction

We have always tried to copy data from file to SQL using either a Windows application or Import Data of SQL, Importing service of SQL has overheads such as Data Type mismatch n bla...bla....bla....

Background

We can use it instead of the BULK INSERT Function provided by SQL as it creates huge transaction Logs which are unmanagable...

Using the Code

Please create the following function to browse a file from your disk.....

sp_configure 'show advanced options', 1 
GO 
RECONFIGURE; 
GO 
sp_configure 'Ole Automation Procedures', 1 
GO 
RECONFIGURE; 
GO 
sp_configure 'show advanced options', 1 
GO 
RECONFIGURE;

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uftReadfileAsTable]') 
   AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
  DROP FUNCTION [dbo].[uftReadfileAsTable]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[uftReadfileAsTable]
(
  @Path VARCHAR(255),
  @Filename VARCHAR(100)
)
RETURNS 
  @File TABLE
  (
    [LineNo] int identity(1,1), 
    line varchar(8000)
  ) 

AS
BEGIN

    DECLARE @objFileSystem int,
            @objTextStream int,
            @objErrorObject int,
            @strErrorMessage Varchar(1000),
            @Command varchar(1000),
            @hr int,
            @String VARCHAR(8000),
            @YesOrNo INT

    select @strErrorMessage='opening the File System Object'
    EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT

    if @HR=0 
       Select @objErrorObject=@objFileSystem, 
              @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',
              @command=@path+'\'+@filename

    if @HR=0 
        execute @hr = sp_OAMethod  @objFileSystem  , 'OpenTextFile',
                                   @objTextStream OUT, 
                                   @command,1,false,0--for reading, FormatASCII

    WHILE @hr=0
    BEGIN
        if @HR=0 
            Select @objErrorObject=@objTextStream, 
                   @strErrorMessage='finding out if there is more to read in "'+@filename+'"'

        if @HR=0 
             execute @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT

        IF @YesOrNo<>0  
            break

        if @HR=0 
            Select @objErrorObject=@objTextStream, 
                   @strErrorMessage='reading from the output file "'+@filename+'"'

        if @HR=0 
            execute @hr = sp_OAMethod  @objTextStream, 'Readline', @String OUTPUT
        
        SET @String = REPLACE(@String,', ',',')
        SET @String = REPLACE(@String,' ,',',')
        SET @String = RTRIM(LTRIM(@String))
    
        INSERT INTO @file(line) SELECT @String
    END

    if @HR=0 
        Select @objErrorObject=@objTextStream, 
               @strErrorMessage='closing the output file "'+@filename+'"'

    if @HR=0 
        execute @hr = sp_OAMethod  @objTextStream, 'Close'

    if @hr<>0
    begin
        Declare 
            @Source varchar(255),
            @Description Varchar(255),
            @Helpfile Varchar(255),
            @HelpID int
    
        EXECUTE sp_OAGetErrorInfo  @objErrorObject, @source output,@Description output,@Helpfile output,
                                   @HelpID output
     
        Select @strErrorMessage='Error whilst '+_
		coalesce(@strErrorMessage,'doing something')+', '+coalesce(@Description,'') 

        into @File(line) select @strErrorMessage
    end

    EXECUTE  sp_OADestroy @objTextStream
    -- Fill the table variable with the rows for your result set
    
    RETURN 
END
GO

Then, you can use the below Query to convert the same into a table: 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mstr_temp]') 
   AND type in (N'U'))
DROP TABLE DIS_mstr_temp

Select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as ID, line as data  
INTO mstr_temp 
from Dbo.uftReadfileAsTable('C:\','output.txt') 

DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT 
        @pivot=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']'
FROM 
        master..spt_values where type='p' and 
        number<=(SELECT max(len(data)-len(replace(data,',',''))) FROM mstr_temp)
        
        IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TEMP_TEMP]') AND type in (N'U'))
            DROP TABLE TEMP_TEMP
SELECT 
    @select='
        SELECT * INTO TEMP_TEMP
        from (
        select 
            id,substring(data, start+2, endPos-Start-2) as token,
            ''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n
        from (
            select 
                id, data, n as start, charindex('','',data,n+2) endPos
                from (select number as n from master..spt_values where type=''p'') num
                cross join 
                (
                    select 
                        id, '','' + data +'','' as data 
                    from 
                        mstr_temp
                ) m
            where n < len(data)-1
            and substring(data,n+1,1) = '','') as data
        ) pvt
        Pivot ( max(token)for n in ('+@pivot+'))p'

EXEC(@select)