Wednesday, July 6, 2011

SSMS and Deleting all records from a self referencing table

Clearing all data from a self referencing table using SSMS can be tricky. Selecting all records in SSMS results grid and pressing DEL just won't work in many times.

Take the common example of Employee - Manager relationship:

If you try using SSMS results grid to delete all records, you may get this error:

No rows were deleted.

A problem occurred attempting to delete row 1.
Error Source: .Net SqlClient Data Provider.
Error Message: The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_Employee_Employee". The conflict occurred in database "test", table "dbo.Employee", column 'ManagerId'.

The statement has been terminated.

Using the Query window and running:
DELETE employee
Will work however.

The difference is that SSMS actually tries to delete row by row, which will violate the constraint. But when using a query to delete all records. SQL Serve is smart enough to clear all data.


Pavel Co Ebele said...

Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Training in Chennai. or learn thru Dot Net Training in Chennai. Nowadays Dot Net has tons of job opportunities on various vertical industry.
or Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.

sriram said...

Nice blog has been shared by you. it will be really helpful to many peoples who are all working under the technology.thank you for sharing this blog.

software testing course in chennai