question

poornima.narayanan avatar image
poornima.narayanan asked

Find a missing Record

How to find the missing Parent Records in a table?
sql-server-7
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
aRookieBIdev avatar image
aRookieBIdev answered
Not the most efficient. If Object_id('tempdb..#ParentNodeS') Is Not Null Drop Table #parentnodes Create Table #parentnodes ( id INT, missingparentnode VARCHAR(50) ) Declare @Id INT Declare @Node VARCHAR(50) Declare @Parent INT Declare @Missingparentnode VARCHAR(50) Declare @Cursor As Cursor; Set @Cursor = Cursor For Select id, node, parent From dbo.parentchildnode; Open @Cursor; Fetch next From @Cursor Into @Id, @Node, @Parent; While @@fetch_status = 0 Begin If ( Len(@Node) >= 3 ) Begin Set @Missingparentnode = @Node While Len(@Missingparentnode) >= 3 Begin Set @Missingparentnode = Substring(@Missingparentnode, 1, Len(@Missingparentnode) - ( Charindex(Reverse('.'), Reverse( @Missingparentnode) ) + ( Len('.') - 1 ) )); If Not Exists (Select 1 From parentchildnode Where node In ( @Missingparentnode )) Begin Insert Into #parentnodes Values (@Id, @Missingparentnode ) End End; End Fetch next From @Cursor Into @Id, @Node, @Parent; End Close @Cursor; Deallocate @Cursor; Select * From #parentnodes
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.