Newbie: updating from txt file.

Question

Post reply

Newbie: updating from txt file.

kevin Gaisford

Newbie

Points: 2
More actions
October 7, 2005 at 9:30 am

#169277

Hi,
Aploigies if this is the wrong forum but I couldn't see a newbie forum.
Can anyone advise why this code doesn't work?
INPUT_FROM mi_ma.txt
UPDATE * from mi_master_def WHERE number BETWEEN 1 AND 50
I get the following error message:
Command Error: Invaild syntax for UPDATE command

Many thanks for any advice.
Kevin

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

You need to specify the field(s) you are updating.

UPDATE TableName SET

ColumnName = mi_master.ColumnName

-- you may need to join the tables here, (i.e., TableName and mi_master)

WHERE [number] BETWEEN 1 AND 50

I wasn't born stupid - I had to study.

kevin Gaisford Newbie Points: 2 More actions · Answer 2

thanks for the reply,

I've now editied the command to this:

INPUT_FROM mi_ma.txt

UPDATE mi_master_def SET number,rpt_name,mgrp_num,fgrp_num,rpt_grp,mi,status,mi_recipe_link

WHERE number BETWEEN 1 AND 50

but am still getting the same error.

the text file I'm inputting from is formatted like this:

2 "Travel Accessori" 5 501 1 "0C" 0 0

seperated by spaces. Does this make any difference? Examples I've seen refer to CSV txt files but mine are space seperated.

thanks for any advice.

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

I completely missed what was happening here.

You need to search this site for parsing your text data into a temp table. This can be done through DTS if the text file is consistent. Otherwise, you may need to split the various values apart into their own columns.

Once you have that data in a table, look up in BOL (Books On Line) how to do an Update and you will see that you must specify the recieving column = the value it should recieve. You will also want to make sure the Update includes a JOIN if the two tables need to be in synch. (This entire may also be accomplished through a DTS package, but you may want to learn it the long way first so you understand what is happening...).

I do not have the time to answer you more thoroughly right now, but chances are good someone else may be able to give you more direction if needed... Folks are VERY good at here at responding well and quickly!

I wasn't born stupid - I had to study.

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

Kevin,

Please explain to me how you get your data into SQL Server. Is it in a one column table? Did you use Enterprise Manager to load it?

Once I understand where you are and how you got there, I, (or someone else) may be able to help you get it into the table you want.

Also, why are you doing an Update rather than an Insert? Does the new text file have a column or value that is shared with the table you want to update?

(I am jumping back and fourth between things, but I will try and keep an eye on this post).

I wasn't born stupid - I had to study.