0

I have an entity framework application. There is a table that is starting to cause a lot of problems, mostly because it doesn't follow EF conventions:

old table

The two important columns are RequestType and RequestDetailId. Each type of request has its own identity column. Since those identities can overlap across tables, there is a RequestType field to uniquely associate an approval to a request.

I am planning on replacing this "One True Lookup Table" with an Approval table and a separate many-to-many table for each request table, linking the request to the approval:

new table

I have a couple of requests already pointing to this new approval table and I like how it is working within EF. However, I am having a hard time migrating from the old table to the new tables. So far, I have been looping through the old data with a cursor, inserting into the new Approval table and using the identity (SELECT SCOPE_IDENTITY()) to insert into the many-to-many table. I need a cursor because I need to keep track of the approval identity value for each new approval record, so I can populate the many-to-many table with it.

I was curious if someone knew a way to do the two inserts without requiring a cursor. Cursors are fine, but they are hard to read.

One thought was to temporarily add a Request ID field to the Approval table, inserting the approval with the request ID, then joining that information to the old table to populate the many-to-many table. After the two inserts, I would drop the request ID column. I don't like the idea of adding a nullable request ID column to an existing table just to do a migration.

I was curious if someone could think of a way to split the old lookup table into the new approval/many-to-many tables without using a cursor.

Travis Parks
  • 8,435
  • 12
  • 52
  • 85
  • 1
    You can use an [`OUTPUT`](https://msdn.microsoft.com/en-us/library/ms177564.aspx) clause to get any data from the rows (Note plural.), e.g. identity column values for newly inserted rows. `OUTPUT` can be used with `INSERT`, `UPDATE`, `DELETE` and `MERGE` and provides access to both before and after values in the case of `UPDATE`. A tool well worth having in your pocket. – HABO Aug 01 '15 at 11:23
  • Have any ideas how to do this prior to SQL Server 2014? – Travis Parks Aug 01 '15 at 13:46
  • The OUTPUT clause was introduced in SQL Server 2005. However, if you insert multiple rows at a time, you still need a unique key copied from the source table to the destination table, to match each IDENTITY value with the source row. – Razvan Socol Aug 01 '15 at 18:19

0 Answers0