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 |
|
jaffa_cree
Starting Member
13 Posts |
 Posted - 2005-08-03 : 03:26:55
|
| Hi,I need to import 2 CSV files regular to two tables.I have defined the table structure, datatypes etc.Problem is when you use bulk insert of DTS import wizard it treats the column values in the csv file as char/strings so the process will fail.What i have done is convert all column datatypes to char to accomodate this.The bulk insert/dts import will work fine now. Problem 1. After the import i will convert the column datatypes back to original. Issue is some of the columns will have a null value, but being from a CSV the null value is actually a string null value. So if the column having these nulls are a smalldatetime or int i cannot convert the column datatype immediately. I run several updates statements to convert to actual null values. Then i proceed with the datatype conversion.As you can see this is a convoluted process and i have created a stored procedure that does all of the above.Are there any other ways taht are more efficient in achieving what i am after with the 2 csv files?Thank you |
|
|
jaffa_cree
Starting Member
13 Posts |
Posted - 2005-08-05 : 22:57:41
|
| any suggestions? |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-05 : 23:40:14
|
| you should bulk insert into a stage table. Then perform a direct insert from the stage table into the final destination table. You can cast and convert in that insert. This will save you from having to alter the table after the bcp is finished.once that is done just truncate the stage table and you are done. btw, you should also drop your indexes prior to the bcp process. once it is done just re-create them.-ec |
|
|
|
|
|
|
Bulk Insert