bulk import - 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

bulk import

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-04-02 : 10:10:35

Hi all,

I have a file data to be loaded to sql using bulk import option
I have 11 | delimiters in it, but I want to load the entire file records into one column,


say for eg. file 

A|B|....|xyz1
A|B|....|xyz22
A|B|....|xyz3

stage_table

column1
A|B|....|xyz1
A|B|....|xyz22
A|B|....|xyz3

BULK INSERT  dbo.[stage_table] FROM 
 '\\UB26\Project\ATTR_M.txt' 
 WITH (
		BATCHSIZE = 50000,
		--FIELDTERMINATOR = '\r\n',
		ROWTERMINATOR =  '\r\n',
		KEEPNULLS
      )
is giving me the below error

The bulk load failed. The column is too long in the data file for row 1, column 1.

-Neil

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-02 : 10:22:31

Use

ROWTERMINATOR = '\n',

instead of

ROWTERMINATOR = '\r\n',

Madhivanan

Failing to plan is Planning to fail

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-04-02 : 10:51:26

Still same error Maddy, I am using DTS now.. but worried it may change the order of rows..

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-04-02 : 12:15:02

Is there any way to load the files as it is, I mean to say the order of records in file should not differ from table records?

-Neil

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-05 : 02:32:34

quote:
Originally posted by aakcse

Is there any way to load the files as it is, I mean to say the order of records in file should not differ from table records?

-Neil

Why does ordering matter?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 05:46:30

quote:
Originally posted by aakcse

Is there any way to load the files as it is, I mean to say the order of records in file should not differ from table records?

-Neil

I dont think there's any reason for you to worry on that, as you can always retrieve the records from table as per your required order using an ORDER BY in SELECT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources