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=listsbody=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=listsbody=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
