MS SQL Server - Problem using BCP

Author

Problem using BCP

Eduardo Sicouret

2006-01-26, 4:53 pm

Hello,

I have the following scenario: I need to migrate several SQL Server tables
to MySQL 5. The tables have aprox. 2'000.000 so i need to use the SQL Server
BCP program because is the fastest.

The problem is with the Null handling. With BCP the Null is a '' empty
string. In my case i'm separating the fields by commas so ",," this would
represent a NULL value.

But with the "Load data infile" in MySQL the NULL value is represented by
this "\N"...

Has anyone resolved this incompatibility? Is there a way to configure BCP to
write the null values as \N ???

How can I handle this NULL incompatibility without editing the resulting
file, because it is several MB in size.

Thank you for your help.

Eduardo Sicouret

KenJ

2006-01-26, 8:23 pm

since you can use a table, view, or query as your bcp source, you may
be able to specify a SQL statement for your bulk export where you use
isNull(columnName, '\N') on all the columns with potential null values.
Along the same lines, you could also create a view that does the NULL
replacement and use the view for your bcp source

Try the bcp utility entry in BOL

Eduardo Sicouret

2006-01-30, 11:23 am

Thank you for answering...

This works fine for string datatypes. but what could I do with numeric and
date datatypes???

Eduardo

"KenJ" <kenjohnson@hotmail.com> escribió en el mensaje
news:1138326809.037866.75220@g47g2000cwa.googlegroups.com...
> since you can use a table, view, or query as your bcp source, you may
> be able to specify a SQL statement for your bulk export where you use
> isNull(columnName, '\N') on all the columns with potential null values.
> Along the same lines, you could also create a view that does the NULL
> replacement and use the view for your bcp source
>
> Try the bcp utility entry in BOL
>

KenJ

2006-01-30, 8:23 pm

Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...

SELECT Isnull(Cast(@int AS varchar(500)),'\N')

KenJ

2006-01-30, 8:23 pm

Since they're just going into a text file anyway, maybe you could just
cast those fields to varchar...

SELECT Isnull(Cast(@int AS varchar(500)),'\N')

Sponsored Links