[dba-SQLServer] Bulk insert

thanks Marty for this information.

Billy


On 5/8/07, MartyConnelly <martyconnelly at shaw.ca> wrote:
>
> Uhh, there is a one line fix to remove trailing blanks in SQL, different
> defaults
> for SQL Server versions and NChar and VChar.
>
> SET ANSI_PADDING OFF
>
> When a table is created with the setting turned on (the default),
> spaces are
> not trimmed when data is inserted into that table. When ANSI_PADDING
> is off, the spaces are trimmed.
>
> So if you SET ANSI_PADDING OFF, create your table, then set it back on
> again, when you bcp the data into the table, the excess trailing
> spaces will be eliminated. The only caveat here is if you have empty
> fields in your file, a single space is inserted instead of a null. If
> this is the case with your data file, you will need to do an update to
> set columns to null when len(yourcolumn) = 0.
>
> See BOL
> http://msdn2.microsoft.com/en-us/library/ms188340.aspx
>
> http://msdn2.microsoft.com/en-us/library/ms187403.aspx
>
>
>
> --
> Billy Pang
> http://dbnotes.blogspot.com/
> "Once the game is over, the King and the pawn go back in the same box." -
> Italian proverb