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