Stored Procedure to import data into SQL Server database... Error -

Stored Procedure to import data into SQL Server database... Error -

i have text file file1.txt in below format. column 1 agusr1 & column 2 agusr2. there 3 records in file:

"agusr1"|"agusr2" "jason "|"debbie " "joy "|"jason " "anna "|"john "

i have written stored procedure upload text file sql server database this:

create procedure sp_import @tablename varchar(200), @filepath varchar(200) declare @sql varchar(5000) set @sql = 'bulk insert ' + @tablename + ' ''' + @filepath + ''' (fieldterminator = ''|'', rowterminator = ''{cr}{lf}'')' exec (@sql)

to execute it, have used statement:

exec sp_import '[db_demo].[dbo].[file1]' , '\\kacl1tsp048\demo\file1.txt'

note : kacl1tsp048 remote server input text file at.

on execution, getting below error -

msg 4863, level 16, state 1, line 1 mass load info conversion error (truncation) row 1, column 2 (agusr2).

the import table schema is

create table [dbo].[file1] ( [agusr1] [varchar](10) null, [agusr2] [varchar](10) null )

for ad-hoc style info imports dispense using mass inserts in favor of selecting file process it. similar creating procedure read file table:

create function [dbo].[uftreadfileastable] ( @path varchar(255), @filename varchar(100) ) returns @file table ( [lineno] int identity(1,1), line varchar(8000)) begin declare @objfilesystem int ,@objtextstream int, @objerrorobject int, @strerrormessage varchar(1000), @command varchar(1000), @hr int, @string varchar(8000), @yesorno int select @strerrormessage='opening file scheme 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 more 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 output file "'+@filename+'"' if @hr=0 execute @hr = sp_oamethod @objtextstream, 'readline', @string output insert @file(line) select @string end if @hr=0 select @objerrorobject=@objtextstream, @strerrormessage='closing 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,'') insert @file(line) select @strerrormessage end execute sp_oadestroy @objtextstream -- fill table variable rows result set homecoming end

now have proc convert file table. still have deal formatting of delimiters run populate [dbo].[file1]:

;with split_names (value,name, xmlname) ( select [lineno], line, convert(xml,'<lines><line>' + replace(line,'"|"', '</line><line>') + '</line></lines>') xmlname [dbo].[uftreadfileastable]('\\kacl1tsp048\demo\file1.txt') line not '"agusr1"%' ) select value, rtrim(replace(xmlname.value('/lines[1]/line[1]','varchar(100)'),'"', '')) agusr1, rtrim(replace(xmlname.value('/lines[1]/line[2]','varchar(100)'),'"', '')) agusr2 [dbo].[file1] split_names

hope helps little?!

sql sql-server sql-server-2008 sql-server-2005 sql-server-2012