CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Srikanth Erukulla
Srikanth Erukulla

Reputation: 49

How to delete complete records from all tables in SQL Server

I want to delete User table, Fruit table and UserFruitMapping table based on UserID.

How can I achieve this?

table structure

Upvotes: 0

Views: 1032

Answers (3)

mvisser
mvisser

Reputation: 670

All these answers are poor and honestly none of you would be touching my databases. You are teaching him the wrong way. Tell me why should you delete from a table when there is no relationship between Fruit and User table? You DELETE only from the HREF/link table for there is the only relationship. Otherwise your database Architecture is badly designed. There should be A CASCADE DELETE on UserID on the Mapping table.

Upvotes: 1

Nazir Ullah
Nazir Ullah

Reputation: 610

Create a simple stored procedure for this 

CREATE PROCEDURE Delete_ThreeTablesData
    @UserId INT
AS
BEGIN
    DELETE FROM Fruit 
    WHERE FruitId = (SELECT TOP 1 FruitId 
                     FROM UserFruitMapping   
                     WHERE UserId  = @UserId)

    DELETE FROM UserFruitMapping 
    WHERE UserId  = @UserId

    DELETE FROM User 
    WHERE UserId  = @UserId
END

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

Deleteing records from all tables can be a really messy task. Especially when there are lots of Foregin Key constraints.

If I find myself in a similar situation, I prefer to Script out the database obviously without the data and just drop the old database.

Finally create a new Fresh database using the script.

Where to find Advance Scripting options

enter image description here

enter image description here

To select "Schema Only" when generating scripts

enter image description here

Upvotes: 0

Related Questions