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 - 2005-09-05 : 09:43:29
|
| Tony writes "Quetsion: I want to bulk insert a text file into database. (SQL 7.0). I seems shouldn't be too hard using bulk insert script. But the problem I am having is that the file contain data with differenct column number. For example the file has 100 records, 40 of them have 5 columns while the others have 10 columns. When I use bulk insert, the records with less number of columns will NOT be correctly imported. Does anyone have some ideas how to solve this. However, using DTS import works fine but that's not the option under my situation. Thanks a lot.Tony" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-09-05 : 15:17:14
|
| A number of solutions.Import into a single column tstaging table and split the data up from there.If there is a lot of data it is faster tobcp the data to a single column staging table.bcp the data out to two files depending on the number of columns (two bcp statements)bcp the data in to the correct tables.Recently I had a requirement for this.Doing the splitting up of columns using a function took several hours.Using a temp table to get the columns sizes for each row and using that for substrings took a couple of hours.Doing the multple bcp's (three files) took about 20 mins.This was on several million rows of data.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Bulk Insert text file with differenct column numbers.