| Author |
Topic |
|
Guestuser18
Starting Member
34 Posts |
 Posted - 2009-08-27 : 13:59:26
|
| Hi GuysI need to do an insert from a txt file into a table in sql server 2005.However the table consists of 6 columns but my txt file has only 5 as I have to specify the other value in the stored procedure.How can I go about doing this?Is there a better way to import a txt file to table other then BULK insert?Thanks |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2009-08-27 : 14:06:13
|
| Is a multiple pass out of the question? In other words, load the table then update the final column?There's always SSIS.An infinite universe is the ultimate cartesian product. |
 |
|
|
Guestuser18
Starting Member
34 Posts |
Posted - 2009-08-27 : 14:43:47
|
| Well the thing is I cant touch the text file and becuase the text file has only 4 columns in it and the table has 5 it throws up an error and doesnt insert any rows.Surely this has been done before.I just need to specify one of the columns in the spThanks for the reply. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-27 : 16:39:11
|
| cat_jesus just gave you two solutions.a) bulk insert to a staging table, then insert from there to your live table.b) use ssis. this won't fail due to the missing column (unless the 5th column doesn't allow nulls)either is a viable solution |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-08-27 : 17:01:37
|
| Here's another option:Create a view of the table, that contains only the 4 columns. Then bulk insert into the view. |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-08-27 : 17:03:39
|
| Or you can use a Format file, to ignore the column. |
|
|
|
Guestuser18
Starting Member
34 Posts |
Posted - 2009-08-29 : 14:02:40
|
| If i use SSIS, can I specify the column I want to update with a date value?Would you recommend using a format file over ssis? |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2009-08-29 : 21:08:24
|
| What's the date value going to be? If it's just something like getdate() then you could use a format file and update the date column after the initial load. SSIS will take you some time, especially if you're not familiar with it. But you can certainly specify the column you want to update with a date value and load in one run. You have a great deal of flexibility with SSIS, but it comes with the price of added complexity.See BOL on BCP and Bulk insertAn infinite universe is the ultimate cartesian product. |
|
|
|
Guestuser18
Starting Member
34 Posts |
Posted - 2009-09-01 : 04:29:46
|
| OK guys Ive set the column date as a default value of the current date so that problem is solved.However in the file I have there are 2 rows of data that need to be inserted but then a number underneath which displays how many rows there are in the textfile. This is causing a problem for my table as it thinks its a row to be inserted and falls over.How can I specify that the number at the end of the file does not need to be inserted?thanks |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-09-01 : 06:24:13
|
| Which option have you choosen to load the data?1. bulk insert (you'd need to remove the line before loading)2. SSIS (conditional split, maybe)Let us know which option you are using, and we can explain further. |
|
|
|
|
Bulk Insert