General discussion

Locked

converting Access queries to SQL stored

By ggokhman ·
I am converting Access 2000 queries to SQL 2000 stored procedures. In Access, when I run a query that updates a key field, it may create a key violation on some records, but Access will ask you if you want to still update just the records that have no key violation.
In SQL, when I execute an Update stored procedure, if one of the records fails because of key violation, all updates are rolled back with an error.
How do I force SQL to take just the "good" records in an Update query?

I tried "SET XACT_ABORT", but it doesn't work in this case. Maybe it cannot be done as easily as in Access and I need to use a cursor and loop throught records and update each one individually.

Thanks in advance for your help.

Gary

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

converting Access queries to SQL stored

by Shanghai Sam In reply to converting Access queries ...

You shouldn't have to resort to a cursor. Try using a WHERE NOT EXISTS clause in your UPDATE query.

For example, say you're updating a table that has two key fields, Key_A and Key_B, and you're changing the value of Key_B in some rows. Your querymight look like this:

UPDATE MyTable
SET Key_B = '12345'
WHERE Key_B = '54321'

Try this instead:

UPDATE MyTable
SET Key_B = '12345'
WHERE Key_B = '54321'
AND NOT EXISTS
(SELECT * FROM MyTable MT2
WHERE MT2.Key_A = MyTable.Key_A
ANDMT2.Key_B = '12345')

I hope this helps.

Steve Diamond

Collapse -

converting Access queries to SQL stored

by ggokhman In reply to converting Access queries ...

I was hoping that there was a Option on the SQL server that can be set to accept the good records, but I guess there is none. Your solution will work just fine, thanks a lot.

Collapse -

converting Access queries to SQL stored

by ggokhman In reply to converting Access queries ...

This question was closed by the author

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Forums