Read File as Table
This function will read in a file from the filesystem and return one table row per line in the file by parsing the file on its cartage-return/line-feed characters (end-of-line). If you also need to split the file into columns, then I suggest you also take a look at the Split Array function.

Please note, you will need to enable SQL Server's "Ole Automation Procedures" to use this function.

Example usage: SELECT * FROM dbo.ReadFileAsTable('c:\', 'Some Text File.txt')

CREATE FUNCTION ReadFileAsTable
(
	@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--WHILE
		if @HR=0
		BEGIN--IF
			Select @objErrorObject=@objTextStream, 
				@strErrorMessage='finding out if there is more to read in "'+@Filename+'"'
		END--IF
		
		IF @HR=0
		BEGIN--IF
			execute @HR = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT
		END--IF

		IF @YesOrNo<>0
		BEGIN--IF
			break
		END--IF

		IF @HR=0
		BEGIN--IF
			Select @objErrorObject=@objTextStream, 
				@strErrorMessage='reading from the output file "'+@Filename+'"'
		END--IF

		IF @HR=0
		BEGIN--IF
			execute @HR = sp_OAMethod  @objTextStream, 'Readline', @String OUTPUT
		END--IF
		
		INSERT INTO @File(line) SELECT @String
	END--WHILE

	IF @HR=0
	BEGIN--IF
		Select @objErrorObject=@objTextStream, @strErrorMessage='closing the output file "'+@Filename+'"'
	END--IF
	
	IF @HR=0
	BEGIN--IF
		execute @HR = sp_OAMethod  @objTextStream, 'Close'
	END--IF


	IF @HR<>0
	BEGIN--IF
		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,'')
		insert into @File(line) select @strErrorMessage
	END--IF
	
	EXECUTE  sp_OADestroy @objTextStream
	
	RETURN 
END

Is this code snippet, product or advice warrantied against ill-effect and/or technical malaise? No. No it's not! Not expressed - Not implied - not at all.