In Part 2 of Replacing Cursors, we look at how to replace cursors in a script where some records are updated while others are inserted.
The second cursor example is the classic simple ETL scenario, where we have a source table and we have a destination table, both comprised of players. What we want to do is look at our source table and say if the player already exists in the destination, just update their information. If they don’t exist in the destination table, go ahead and put them in. That’s our update and our insert that you can see as part of the conditional logic here. First thing we want to do is identify the cursor language and we want to get rid of it because, again, we’re taking a cursor and making it set based, so we’ll get rid of the cursor language.
Another thing we can do here is get rid of the “declare variables” statement at the top. Those variables were there really just to drive the cursor and now that we don’t have a cursor, we don’t really need the variables either, so let’s get rid of those. Looking at what we have left, we’ve got a select statement, we’ve got our “if conditional logic” and we’ve got the update in the insert. Just to tidy things up a little bit, I don’t really want to look at that “else” anymore. I don’t want to have to work around that. Let’s just take the “else” out of the equation too and simplify it just a tiny bit more.
Okay, where do we go from here? We’ve got a select, an update, an insert, and then a line of conditional logic. What we need to do now is take that select that used to drive the cursor and attach it to the update and the insert statements. Syntactically they won’t be exactly right. We’ll fix that up later, but for now, we just want to have things arranged so we can continue working. We’ll take the update statement, move it up, take the insert, move it up, and we’ll attach the select statement to the insert as well. Now the select statement is driving both the update [00:02:00] and the insert.
The next step we want to do is figure out how we can take that conditional logic and the “if exists” statement and put it in the select statements that are driving the update and the insert. With the update, it’s fairly simple. We just want to take the “where” clause from the conditional logic, the “where player ID equals player ID” and put it in the update statement. What we’ll do is copy that up and, because we can’t use that variable, we’ll just say, “where the destination player ID equals the source player ID.” It’s really the same thing. We’re just leaving the variable out of the equation.
With the insert, it’s a little trickier because we can’t use quite the same shorthand with our T-SQL. We’ve got to be more explicit and we have to do a left join back to the destination table. With the updated sort of implied, but the insert, SQL Server doesn’t like that, so you have to be more explicit. We’ll do a left join to the destination on the player ID in the destination is the player ID in the source, and we have to say where there’s no matching record in the destination table. The way we’ll do that is to say where the destination player ID is null.
Now that we have that conditional logic built into the update and the insert statement, we can get rid of the “if exists”. We don’t need that anymore, so we’ll carve that out. Now from here, there are a few little changes we need to make. Now that the conditional logic is gone, we’ve got this pretty clean. We have exactly what we wanted, an update and an insert statement. The next step from here is to polish them up, make sure that syntactically they’re correct and the code will run. Looking at that update statement, you can’t really drive an update with a select, can you?
What we need to do [00:04:00] is change that. We need to have it say “update” and then set the fields and from source where destination equals source. Let’s change that select statement in the middle, get rid of the select and what we want to do is change it to “from source”, move “from” and the “where” down and replace what goes in that ellipsis. Now we say, “update destination”, “set destination HP equals source HP”.
All right, we’re done there. Let’s look at the insert statement now and we want to get rid of the ellipsis there. Really, the only thing we need to clean up is we need to make sure that those columns are specified on the insert side of things. The select on down, that’s all fine. With the insert, we’ll just say, “insert destination (player ID, HP)”. Now this would work without that specification if we knew that the table consisted only of player ID and HP in that order, but we don’t want to take that chance.
All right, good work. Let’s move on to a more complicated cursor example.
- 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)
- 10. Replacing Cursors, Part 3 (7m)
- 11. Let the Adventure Begin! (9m)
- 12. About Doug (2m)