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.
| Author |
Topic |
|
OnezerO
Starting Member
4 Posts |
 Posted - 2004-10-06 : 12:47:40
|
| Is it possible to import blank or null fields from a flat ASCII file into a table field as numeric or decimal and if so what is the trick?I keep getting a data conversion error when I attempt this, however everything imports fine if I change the datatype on all of the table fields to char, but this defeats the purpose.Thanks. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-06 : 13:44:37
|
| import into a staging table with varchar() columns as step 1. then execute a query to move the data -- while converting as necessary, handling NULLS or blanks as you wish -- to the actual tables.this gives you can extra opportunity to perform some validation on the data as well, before it enters your primary tables.- Jeff |
 |
|
|
OnezerO
Starting Member
4 Posts |
Posted - 2004-10-06 : 17:45:17
|
| Thanks for the reply jsmith8858.This sounds like it should handle the job. You wouldn't happen to have a sample query that moves and converts the field data from one table to another would you?I'm a bit of a newbiwe and a helpful query would save me a lot of time. Thanks. |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2004-10-07 : 04:41:31
|
| This will depend upon the structure of your staging table (and, therefore, that of your ASCII file). Just lookup CONVERT and COALESCE in Books Online.Mark |
|
|
|
OnezerO
Starting Member
4 Posts |
Posted - 2004-10-07 : 09:34:39
|
| Thanks mwjdavidson and jsmith8858 for your replies. They have been a big help.I have found the syntax and samples of the CAST and CONVERT commands that should do the trick! |
|
|
|
|
|
|
Numeric Import