title: Dynamic SQL query based on a hyphen in a varchar column
body: <p>I have a question about a specific query I'm writing in a <code>SQL Server 2008</code> database. This is part of a (much) larger query, but I'll simplify the hell out of it to make it easier to isolate what I'm trying to do.</p>
  <p>I have a table <code>TableX</code>, two columns:</p>
  <ul>
  <li><code>IDX (int) identity</code></li>
  <li><code>VarX (varchar(20))</code></li>
  </ul>
  <p>I have two auxiliary tables, <code>TableY</code> and <code>TableZ</code>. Both have two columns,</p>
  <pre><code>TableY
  </code></pre>
  <ul>
  <li><p><code>IDY (int) identity</code></p>
  </li>
  <li><p><code>VarY (varchar(20))</code></p>
  <p>and <code>TableZ</code></p>
  </li>
  <li><p><code>IDZ (int) identity</code></p>
  </li>
  <li><p><code>VarZ (varchar(20))</code></p>
  </li>
  </ul>
  <p>I am doing an insert into a fourth table, we'll call it <code>TableA</code>. <code>TableA</code> has three columns:</p>
  <ul>
  <li><code>IDA (int) identity</code></li>
  <li><code>IDY (int)</code></li>
  <li><code>IDZ (int)</code></li>
  </ul>
  <p>The IDY and IDZ fields in this table are foreign keys to the identity columns in Table Y and Table Z.</p>
  <p>Sounds complicated, but its a really simple connection. Here's where it gets fun though.</p>
  <p>So the data in <code>TableX</code> might look like this:</p>
  <pre><code>IDX | VarX
  --------------
  1   | ABC
  2   | ABC-LMN
  </code></pre>
  <p>The data in <code>TableY</code>:</p>
  <pre><code>IDY | VarY
  ----------------
  1   |ABC
  2   | HIJ
  </code></pre>
  <p>The data in <code>TableZ</code>:</p>
  <pre><code>IDZ | VarZ
  ----------------
  1   | LMN
  2   | OPQ
  </code></pre>
  <p>Basically, <code>VarX</code> (the second column), is either going to be a single varchar string, or two connected by a hyphen, no spaces. In the case of a single string, with no hyphen, its always a match in Table Y. The match would be on <code>VarY</code> in <code>TableY = VarX</code> in <code>TableX</code>. I would grab the associated ID (<code>IDY</code>), and use that in the insert on <code>TableA</code>.</p>
  <p>In the example of row 1, the insert into <code>TableA</code> would be <code>(null, 1, null)</code></p>
  <p>Now in the case of row 2, it has two strings separated by a hyphen. So the insert into <code>TableA</code> would ultimately be <code>(null, 1, 1)</code>.</p>
  <p>So my question is this... how do I formulate the insert with its corresponding joins to deal with this type of logic? I'm sure it has to be a case statement... just having alot of trouble visualizing the full query, as I'm no DBA.... Any help is appreciated.</p>