That's why I love Textpad and regular expressions

Suppose you have a lot of ALTER TABLE statements, like the one below (I actually had 30+) and you find out you must delete inconsistent data on child table before procceeding on constraint creation. 

ALTER TABLE dbo.ChildTable
       ADD CONSTRAINT FK_ChildTable_ParentTable
              FOREIGN KEY (ChildField)
                             REFERENCES dbo.ParentTable (ParentField)
GO


What would you do? Manually add a DELETE statement before each ALTER table statement? No, in my case, I used Textpad and regular expressions. You can check just below

Find: ALTER TABLE[ ]+\(.+\)\n[ ]+ADD CONSTRAINT[ ]+\(.+\)\n[ ]+FOREIGN KEY[ ]+(\(.+\))\n[ ]+REFERENCES[ ]+\(.+\)[ ]+(\(.+\))\nGO\n
Replace with: DELETE \1\nFROM \1\n LEFT JOIN\n \4 ON \1.\3 = \4.\5\nWHERE \1.\3 IS NOT NULL AND \4.\5 IS NULL\nGO\n\n&\n

and then I got a pair of SQL statements for each ALTER TABLE, like the one below 

DELETE dbo.ChildTable
FROM dbo.ChildTable
  LEFT JOIN
     dbo.ParentTable ON dbo.ChildTable.ChildField = dbo.ParentTable.ParentField
WHERE dbo.ChildTable.ChildField IS NOT NULL AND dbo.ParentTable.ParentField IS NULL
GO

ALTER TABLE dbo.ChildTable
       ADD CONSTRAINT FK_ChildTable_ParentTable
              FOREIGN KEY (ChildField)
                             REFERENCES dbo.ParentTable (ParentField)
GO

2 Comments

Comments have been disabled for this content.