Posted by Erland Sommarskog on 05/22/05 13:35

akej via SQLMonster.com (forum@SQLMonster.com) writes:
> In my aoverhead post i wanted to show u as an example that i can covert
> data e.g. from char to int, in my case when i accomplish BULK INSERT i
> want to convert from suppese char to int. If in my data file one column
> is char i need in some way to cenvert it to int like i did in my
> overhead post with CASE statement.

Normally, when I hear a conversion I think in terms of implicit conversion
as when the string literal '20000520 12:21:31' can be interpreted as a
datetime value, or when you use explicit conversion with cast() or
convert().

The only form of conversion you can do with bulk load is implicit
conversion, since you cannot apply functions to the data you load. From
this follows that you neither can do user-implemented "conversion"
as in your example with bulk-load directly.

If you need to do transformation like storing TRUE/FALSE in an input
file as bit values, there are two choices: 1) use an intermediate
table into which you load the data, and the use INSERT-SELECT to move
the data to the target table. 2) Use DTS to write a transformation task.

As for 1), this is often needed anyway, because the file data may be
imperfect and need scrubbing of duplicates etc. As for 2) I assume that
this is what you can use DTS for, but never having used DTS myself, I
can give any details. It's possible that running the Import/Export
wizard can give you headstart in this area.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]