June 12, 2008 at 12:49 pm
I'm automating a ridiculous process that used MS Access and about 40 different scripts to upload data from MS Excel an oracle database on a monthly basis. I'm writing a stored procedure from my SQL 2000 server that will be called by DTS. I'm at the very end, and the previous process updated a table on the oracle database using the statement:
UPDATE ORACLETABLENAME SET ID = ROWNUM;
I'm not really an oracle guy, but I understand that ROWNUM is not really a column, but that every row has a rownum.
I'm trying to replicate this from my SQL server with the command:
update LINKSERVERNAME..SCHEMA.TABLE table set ID = ROWNUM but SQL reports "invaild column name"...
any thoughts on the best way to accomplish this?
June 12, 2008 at 1:03 pm
You are in a SQL 2005 forum - if you actually were running SQL 2005 this would not be that complex since you get a rownum function in 2005. In your case, however, it is a bit more complicated.
The easiest solution for you would be to pass the entire update statement through to the Oracle end and then use the oracle rownum function. This could easily be done by creating an oracle procedure and just calling it from your SQL server.
As another option - if you want SQL to figure out the rownum for you - use a tally table to determine the rownum in your data set. Jeff Moden has a bunch of articles about tally tables on this site that will help you figure this out.
June 12, 2008 at 1:18 pm
Thanks for the info and sorry bout the wrong forum... I dont know oracle well enough to do it from that side... I'll do some reading on the tally table.. Thanks!
June 12, 2008 at 1:30 pm
went the other way... created the oracle procedure... cant figure out the syntax to call it from SQL?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply