Delete last row in table 2

[micang]

SQL 2000

Hi All,

I import data(29 columns) into a SQL table from a txt file. On import I add an identity column(uniqueID).

The data has a end "marker" which is the last row and I need to delete the whole row as it's not actual data.

This is the way I have been doing it:

delete from table where uniqueID = (select max(uniqueID)
from table)

..but am not sure if there is a better method to identify the last row instead of using the identity column in order to delete this row?

Any info appreciated as always.

Michael

 

[Crowley16]

are you sure that it's the very last row to be inserted?

if so, there is a function in sql called scope_identity() which returns the identity of the last record inserted within the current scope.

you could use this as part of the import script...


--------------------
Procrastinate Now!

 

[micang]

Yes, it is always the last row in the text file.

Thank you Crowley, I was not aware of scope_identity.

Michael

 

[Crowley16]

there is also @@identity but use this with a lot of care since it brings back id's not within the scope, i.e. if you had a trigger on that table, when using @@identity, it will actually show you the new id on the record inserted by the trigger, not the one you just inserted...

--------------------
Procrastinate Now!

 

[ESquared]

scope_identity() will work but only if you inserted to that table in the current session.

ident_current('tablename') can also work no matter what session the last row was inserted from, but be careful for a table that you did not JUST insert the last row to as, though it will tell you the current "max" identity value of the table, this is more about the next row to be inserted. If you delete the last row and check ident_current again it will not have changed.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[r937]

The data has a end "marker"...

then i would use this to target the row to delete, rather than monkeying with identity functions and such

delete 
  from daTable 
 where somefield = 'marker'

r937.com | rudy.ca

 

[micang]

Thanks guys.

I think I can get away with using scope_identity.

Appreciate leading me in the correct path.

Michael

 

[micang]

Thanks r937 - I could use that, but I must make sure this marker remains the same each time I receive this file (every quarter) - but yes, that is also an option.

Many thanks.

Michael

 

[SQLSister]

The reason why it would be better to use the marker is that if some day they change the feed and it no longer has that record, your process will not delete anything. With your current process the last record is deleted no matter what. It is almost always better to delete based on the content of the record instead of it's place in the file.

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

Rudy and SQLSister are right: if there is a marker this is better to use.

 

[micang]

Thanks guys, that is true, the much better option, I will utilize this marker to achieve my goal.

Thanks to everyone, I have learnt a lot from this thread.

Michael