We help IT Professionals succeed at work.

SQL Microsoft: the row unique, but reuse id (recreate record for testing)?

janhoedt
janhoedt used Ask the Experts™
on
Hi,

I have a sql table with unique id  in which I d need to test frequent removal and adding of the same record.

Can I somehow make sure ONLY this record gets the same id again and again (delete/recreate record endlessly)?

J
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Sure, use a transaction.
BEGIN TRAN

-- SELECT the rows in which you're going to insert and delete
SELECT 'Before' as label, blah, blah, blah
FROM whatever
WHERE unique_id = 42 -- replace with your value

-- Do your test inserts and deletes here

-- Verify that the above inserts and deletes worked successfully. 
SELECT 'After' as label, blah, blah, blah
FROM whatever
WHERE unique_id = 42 -- replace with your value

-- Then reverse everything so that none of the above actually commits.  
-- When you're ready to do this for real change ROLLBACK to COMMIT. 
ROLLBACK TRAN

Open in new window

Author

Commented:
Thanks but what is a transaction/How do I implement the code you mention?
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>what is a transaction
A method to ensure that a block of code either executes completely, or not at all.  See TRANSACTIONS and the supporting links at the bottom of that page.

>How do I implement the code you mention?
Since you didn't post any code for experts to work with, I don't know how I can make what I provided any easier.