CODEWITHSUNDEEP
sqlsql-server-2014
Jessica Nutt
Jessica Nutt

Reputation: 67

Advice on removing records completely from a database

I am looking for some advice on the best way to remove multiple records (approximatley over 3000) completly from a database. I have been assigned a job of removing old records from our database for GDPR reasons.

However this is a database i do not have much knowledge on and there is no documentation, ERD's etc on how the tables are joined together.

I managed to work out the tables which will need to have records removed to completely remove details from the database, there are about 24 tables which need to have records removed from.

I have a list of ID numbers which need to be removed so i was thinking of creating a temporary table with the list of IDs and then creating a stored procedure to loop through the temproary tables. Then for each of the 24 tables check to see if it contains records connected to the ID number and then if they do delete them.

Does anyone know if there is any better way of removing these records??

Upvotes: 0

Views: 63

Answers (1)

Arkaine80
Arkaine80

Reputation: 186

I would use a table variable and union all:

declare @ids table (id int primary key)

insert into @ids (id) 
select 1 union all
select 2 union all
...
select 3000

delete from table_name where id in
(select id from @ids)

Obviously just change the numbers to the actual ids

Upvotes: 1

Related Questions