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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-17 : 13:50:04
|
| Scott writes "I'm dealing with some big data files and want to use the bulk insert to populate my sql tables. My data files are flat fixed and I am using the bulk insert with a format file. One problem I came across was all my field types needed to be (char) for the bulk insert to work. But my main problem is how to scrub the data after it is inserted. Currently I have a vb app that does all this for me but it is not fast enough. I know I can use vb to scrub the data after it has been inserted, but the movenext method kills me. It will not be any faster than my current vb app. I'm also trying to stay away from DTS.This is what I want to do. Bulk insert, scrub, then I need to set the field types properly (because they are all char).Do you have any suggestions?Thanks" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-17 : 13:55:59
|
| We'll need some more details on the exact scrubbing you need to do, but this should get you started.Once you've imported the data, you can use various SQL functions to test data. For instance, if you imported date values and want to make sure they're valid, you can use the ISDATE() function:SELECT * FROM stagingTable WHERE IsDate(dateCol)=0That will display rows that do not have valid date values. There are similar functions for IsNumeric().For pattern matching, you can use the LIKE operator or PatIndex() function to test that character data matches a certain format:SELECT * FROM stagingTable WHERE PhoneNumber NOT LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'This will find all rows with a phone number that doesn't match the nnn-nnn-nnnn pattern.HTH. If you can further describe what exactly needs to be fixed we can provide more help. Thanks. |
 |
|
|
|
|
|
Scrubbing data after bulk insert