I am not a guru - but may have a work-around until someone gives you the
best answer:)
Set up the text file as an odbc text datasource, and let CF read it in one
at a time, then update the sql database.
OR
bcp it into a temporary table, then use that table to do the update of the
live table..
Al
>Hello everyone,
>
>Got a problem here that I can't seem to figure out. I have a tab-delimited
>text file containing 3 cols, (can_id, votes, % votes) that I need to import
>into a SQL 6.5 table containing these three columns (and others) that needs
>to be updated on a regular basis - updating existing records, not inserting
>new ones.
>
>My first guess would have been bcp, but as near as I can tell, this only
>works on data copying operations, so it won't allow me to perform an UPDATE
>type of action on the table I want to update. I'm used to working with SQL
>7.0, which would let me save this as a DTS package, so my t-sql stored proc
>syntax is a little rough.
>
>Here is what I've got so far, which is full of errors:
>
>if exists (select * from sysobjects where id =
>object_id('dbo.update_election_output') and sysstat & 0xf = 4)
> drop procedure dbo.update_election_output
>GO
>
>/*******************************************
>Object: Stored Procedure dbo.update_election_output
>Script Date: 8/7/00 11:15am
>Last Updated On: Never!
>*******************************************/
>
>CREATE PROCEDURE update_election_output
> @election_results_file varchar(255)
>AS
>
>/*******************************************
>Update election results from FTP source file.
>*******************************************/
>CREATE TABLE #election_updates
>(
> textstring varchar(255) null
>)
>
>select 'Getting Election Results'
>
>INSERT #election_updates
>EXEC ('EXEC master..xp_cmdshell ''type '+@election_results_file+'''')
>
>select 'Updating Election Results Table...'
>
>
>UPDATE a
>SET a.votes = SUBSTRING(textstring,8,15)
>SET a.percent_votes = SUBSTRING(textstring,16,23)
>FROM elections_output a
>WHERE EXISTS
> (
> SELECT *
> FROM #election_updates
> WHERE a.can_id = SUBSTRING(textstring,1,7)
> )
>/*******************************************
>Cleanup and Grant appropriate permissions.
>*******************************************/
>DROP TABLE #election_results
>GO
>
>Is this the right way to go about this? Would it be better to use bcp to
>copy the data into a temp table and then write an update statement to update
>the master table?
>
>Thanks,
>Ryan
>------------------------------------------------------------------------------
>Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
>To Unsubscribe visit
>http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
>send a message to [EMAIL PROTECTED] with 'unsubscribe' in
>the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
