August 25, 2004 at 3:18 pm
Hi all,
I have a table that needs to be updated from a txt file via a where clause.
Does anyone have a sample of how to go about this?
I have a unique username in the table and a corresponding one the txt file. I would like to test each row to see if the name exists and update a column from the txt file, then loop to the next row.
I.e. fred = fred (yes) then update house from txt file.
I could, if easer create a temp table in database.
Regards Steve
August 25, 2004 at 4:27 pm
Dealing with a text file, I think a temporary table may be your best bet.
Steve
August 26, 2004 at 1:40 am
Definately use a temp table, import the txt file to the temp table, and manipulate your data there, then just copy it out to where you need it at the end!
Julian Kuiters
juliankuiters.id.au
August 26, 2004 at 6:24 am
Set up a Meta Data Services package. I have one that exports staff information to a .csv file for importing into a palm. It does it automatically once a day. You could set up a package that imports periodically.
First, set up an import package that specifies which fields in the .csv file go to which fields in your table. Then schedule the package to import however often you want.
Isaac
August 26, 2004 at 7:01 am
If you use a temp table it has to be Global temp table.
They way we do this is, we built a stored procedure that creates the table, then we use the bulk insert command to insert the file into the table. We then do a insert into statement into the table and drop the created table.
Doing it this way adds some flexibility, you can schedule as a job or you pass different file names to it. You can also keep a stats table for the date and time that it ran and how many records it imported.
August 26, 2004 at 9:22 am
It would only have to be a GLOBAL temp table if you are using bulk insert, or otherwise accessing it from separate connections. If the insert and all the manipulation is done within a single stored procedure, a local temp table will work fine.
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply