Bulk Insert works but leave's spaces in data

Question

Bulk Insert works but leave's spaces in data

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 1

Your solution is to either don't use Bulk Insert (use SSIS and trim the data on import) or import the data into a staging table, trim the spaces off, then insert it to its final destination.

Bulk Insert is not a command that is meant for data fiddling. It just inserts the data in the file. That's it. So far as I know, not even a format file will assist you because it's just looking for columns of different datatypes, different column order, or different # of columns.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

eddy-961438 SSC Veteran Points: 292 More actions · Answer 2

eddy-961438

SSC Veteran

Points: 292

April 23, 2012 at 4:51 am

#1477386

its a shame because it works very fast.

thanks

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 3

eddy-961438 (4/23/2012)
its a shame because it works very fast.

That's why the command works so fast, because it's not transforming data.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Try to drop and recreate destination table with SET ANSI_PADDING OFF, it's still supported in SQL 2012

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 5

Eugene Elutin (4/23/2012)
Try to drop and recreate destination table with SET ANSI_PADDING OFF, it's still supported in SQL 2012

Note to OP: If you have data in this table and want to follow this advice, you need to archive it off somewhere so you won't lose it. Never drop a table without first double-checking that you can lose something without consequence.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.