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 |
|
magictech
Starting Member
44 Posts |
 Posted - 2004-09-18 : 02:30:37
|
| I'm getting an error message when I try to bcp in a table that has a newly added column. This new column is added at the end of the table. I'll use the authors table in the pubs database to explain this problem.Here is what I want to accomplish.Bcp data out of the authors table to c:\city\authors.txt.Truncate the authors table.Add a new column to the authors table.Bcp data in to the authors table from c:\city\authors.txt.My problem is, the first three steps of these process work fine, except the step where I've to bcp data back into the authors table because of the newly added column. • The first problem I encounter is that while bcping in the authors table, the newly added column get populated with some data that I don't know where it came from. • The second problem is the bcp in process stop after importing just two rows with an error message.These process work fine if the structures of both tables are exactly the same.Does anyone know why I might be having this problem? Any information would be greatly appreciated. |
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-09-18 : 03:04:45
|
| The table has to have the same number of columns as the data file you are loading in. You can get around this by creating a view of the authors table and omit the new column from the view, then bcp into the view.--KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-18 : 17:05:07
|
| Or you can use a format fileseehttp://www.nigelrivett.net/BCP_quoted_CSV_Format_file.htmlBut a view is simpler.==========================================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. |
|
|
|
magictech
Starting Member
44 Posts |
Posted - 2004-09-20 : 18:37:03
|
| Thank for the responds. I decided to go with the solution that requires creating a via and then Bcp in the data via the view. However, I just found out one of the column names on my table is a SQL server reserve word (Key).First when I tried to create the view, I get an error message that says "Incorrect syntax near the keyword 'key'". I went ahead and add bracket on the column name [Key]. This time the view was created successfully.When I tried to bcp in the data to my table via the view that was created in the previous step, I get the same error message I've had before when I was trying to create the view initially "Incorrect syntax near the keyword 'key'". The Bcp process failed. Does anyone know how to get around this problem? Is there any special consideration that I need to know? Any help would be greatly appreciated. |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-09-20 : 18:55:08
|
| Put brackets [] around all occurances of the word key. Better yet, change the column name to something that is not a reserved word.An alternative; if you are using bcp to load into the view I am suprised bcp cares that key is a reserved word, but as a workaround, create the view like thiscreate view v_myview asselect col1, col2, [key] as keyalias, col4, ...then bcp will not even see a column named key.--KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
|
|
|
|
|
|
|
|
Bcp in a table that has a diffent structure