This last example is the most complicated of the three cursor examples. Don’t feel bad if you have to go back and watch it an extra time or two to figure out exactly what’s going on. What we’re going to do here is we’ve got a Player table, a Player Quest and a Player Roll. The Player table is the master table. It’s the source of the player ID. It’s the one on the one to many side. The Player Quest and Player Roll have a many to one relationship back to the Player table. What we want to do is make sure that when we bring the Player table into a new system and assign it a new player ID, that that relationship to the other two tables is preserved and they also get the new player ID when they arrive there.
If we are to do this using a cursor, it might look something like this, where we would have to declare new ID and a cursor for the master table. We would insert the first row of the master table and then we would get back the new ID because it would be an identity column. We’d have to take that new ID and then run an additional cursor inside of this one for each related table. Player Quests, Player Rolls, we’d insert those rows using a new ID, loop, close and then repeat that inner cursor for each additional detail table. We’d have to do it once for the Player Quest, once for Player Rolls. That’s a lot of cursor looping, a lot of row by row operations. There’s a way that we can do this using set-based operations. In order to do that, we’re going to need what it known as a bridge table.
A bridge table is simply a table that contains the old ID, the new ID and [00:02:00] a composite key that uniquely identifies each row. That key is made up of one or more columns in the table data. The way that we would use the bridge table is five steps. First, we create a composite key that uniquely identifies each row. We’d insert the old ID and the composite key into the bridge table. We’d import the master table rows, update the bridge table with a new ID, joining on the composite key and then import the detail table rows, joining on the bridge table to get the new ID as part of that insert process.
Step one is to create a composite key. We need to find something that can uniquely identify each row on the table just based on the columns we already have, not including the ID. In this case, what we’re going to do is take the player name, we’ll use a pipe delimeter and then we’ll use a player class ID, put another delimeter after that and the player level. We’ll use that as the composite key. The next step is to insert the old ID and composite key into the bridge player table. We’ll have the old player ID and the composite key in there. You can see that first row has Hashmatcher and then class ID 2, level 4. All we’re waiting on now is the new player ID which is just PlayerID. That will get added in step four.
The next step, insert master table rows. We want to take the player name, player class ID, player level and insert them from the old to the new. Now, we don’t select the old or new ID’s because they’re identity fields. Those are taken care of already. We don’t want to insert the old one and we can’t tell it to insert the new one. We just leave that out of the insert statement. The next step is to update the bridge table with a new ID. We would update the bridge player table using the player table. What we would join on would be, in the bridge player table, [00:04:00] the composite key and on the new player table side, we would rebuild that composite key for the join, so player name, player class ID and player level. This process fills in player ID so we now have player ID 1, 2, so on and so forth. That completes our bridge table.
The last step then is to insert the detail table rows. We would insert into the Player Quest table using the bridge player ID which is the new player ID, the old Quest ID, and we would join the Player Quest old table to the bridge player ID on the old player ID. It sounds more complicated than it really is. Once you’ve got it built, it will seem fairly straightforward. You can repeat this process with every detail table.
Hello? You’re still here. I was afraid that after all that texts I’ve thrown at you and all the yapping I’ve done from off-screen, maybe it began to tune me out. But I’m glad you stuck with it. In that last segment, we learned that if you have to take a master table and related detail tables and bring them into a new database and assign new keys in the process, there’s really no beating a bridge table. It can take a cursor based process that would take hours and cut it down to seconds. Because, again, just one insert per table.
Now, I know I’ve thrown a lot at you. You may even want to go back and rewatch that last part. But I think we’re at a point now. We’re ready to put your new skills to use. Take a deep breath. Get a good stretch going on. It’s time to set out on your adventure.
- 01. Prologue and Script Files
- 02. Thinking in Sets (5m)
- 03. Number and Date Tables (10m)
- 04. Case Expressions (17m)
- 05. Computed Columns (10m)
- 06. Windowing Functions – Design (21m)
- 07. Windowing Functions – Performance (8m)
- 08. Replacing Cursors, Part 1 (6m)
- 09. Replacing Cursors, Part 2 (5m)
- 11. Let the Adventure Begin! (9m)
- 12. About Doug (2m)