determine which rows were affected

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

determine which rows were affected

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2005-01-10 : 19:21:00

hey all,

just wondering if there is a way to determine which rows were affected by a insert update or delete statment

i know you can use @@ROWCOUNT to find out how many rows where changed but what about finding the particular row(s) updated and possible the old values compaired to new values?

is there any easy way ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-10 : 19:25:24

You would have to either implement something or use triggers. In triggers, the inserted and deleted tables contain the information.

Tara

Ex
Posting Yak Master

166 Posts

Posted - 2005-01-10 : 19:32:55

though so :) that is what i have been doing just using trigger and working out differences

ok kewl was on the right track thanks tara

Kristen
Test

22859 Posts

Posted - 2005-01-11 : 01:32:31

When I'm doing some manual

INSERT INTO TargetTable
SELECT *
FROM SourceTable

type stuff and I get an FK error I try running with

SET ROWCOUNT n
INSERT INTO TargetTable
SELECT *
FROM SourceTable
ORDER BY PrimaryKeyFields
SET ROWCOUNT 0

and run repeatedly starting with large values of n until I get the FK error and then reduce n by 50% each time until I get down to 1.

Then a

SELECT TOP 1 *
FROM SourceTable
ORDER BY PrimaryKeyFields

should tell me the offending row.

But I don't understand why SQL Server can't give me a clue in the error message - I mean, it's got the offending data right there in memory :-(

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources