About a year ago I was working on a project that required that data be extracted from one database and inserted into an database. Not the most earth shattering concept. This is done quite often and doesn’t necessarily require a discussion of foreign keys.
So why talk about them… well… in this case the source and the destination databases were identical. They had the same table and foreign key structure. But no reason to be sold on this discussion quite yet… because it would be east to just build the ETL process around this. It would need to be built with the ordering of the tables controlled with precedence constraints or inserts statements in the proper order.
So here’s the rub… this process needed to move data between hundreds of tables. When hundreds of tables are in the discussion, I lose interest very quickly in trying to determine by hand how to order the tables. I did in this case and looked for a method to accomplish this through foreign key meta data.
And of course, there wouldn’t be a post if there wasn’t an answer.
Well, maybe I’d post a question, but I not so nanner nanner. The solution uses a function with a query to get the necessary data. There might be better ways to do this, but this way worked quite well and had pretty decent performance. One variation of this used a single CTE that I let run for a couple hours before I decided it was running sub-optimal.
There are two caveats with this solution:
- Self-referencing foreign keys will cause a recursion error. When row A needs to be inserted before row B can’t really be helped through metadata. Since the environment I was working in didn’t have this issue, the solution didn’t address it either.
- Circular-referencing foreign keys will also cause a recursion error. There are situations where table A references table B which references table C which then references table A. I run into these from time to time but not in the environment that this was designed for.
With the caveats out of the way, here is the solution: