Help needed for b ulk Insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help needed for b ulk Insert

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-01-29 : 10:24:24

Hi all,

I have some files to be loaded into correspounding tables in sqlserver dbo schema.

I have a table Tab( col1, col2..Coln,coln+1,coln+2,coln+3,coln+4) and a view Tab_View created by taking all the cols from table except last 4

And the data file contains values for col1...coln the other 4 columns are updated after insert from file is success.



ALTER PROCEDURE [dbo].[sproc_Load_Data] @FILENAME VARCHAR(1000), @SCHEMA_NAME VARCHAR(100), @TABLE_NAME VARCHAR(100), @DELIMITER VARCHAR(10) AS

DECLARE @SQL VARCHAR(MAX)
-- Load the data
SET @SQL = 

'BULK INSERT [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + '_VIEW] FROM ''' + @FILENAME + ''' WITH (
		BATCHSIZE = 50000,
		FIELDTERMINATOR = ''' + @DELIMITER + ''',
		ROWTERMINATOR = ''' + CHAR(13) + CHAR(10)  + ''',KEEPNULLS)'
exec(@SQL)

The data in the data file is as below

A111|B222 |PO|NBC|1994-04-05 16:15:21.717|LEPP | +0
A111|B222 |PO|NBC|1994-09-15 09:21:54.894|LEPR | +0
A111|B222 |PO|NBC|1994-09-15 09:21:54.894|LERR | +0
A111|B222 |PO|NBC|1995-10-12 10:16:23.254|LACC | +0

exec [sproc_Load_Data] 'c:\DATAFILE1.TXT','DBO','TAB','|'

Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 7. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-01-29 : 10:52:33

is there any more information needed? plz let me know, I am using sqlserver 2005.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-01-29 : 11:02:12

Thanks all I got it now, the row terminator is only line feed not carriage return, just changed it to char(10) it worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources