title: Split one large, denormalized table into a normalized database
body: <p>I have a large (5 million row, 300+ column) csv file I need to import into a staging table in SQL Server, then run a script to split each row up and insert data into the relevant tables in a normalized db. The format of the source table looks something like this:</p>
  
  <p><code>(fName, lName, licenseNumber1, licenseIssuer1, licenseNumber2, licenseIssuer2..., specialtyName1, specialtyState1, specialtyName2, specialtyState2..., identifier1, identifier2...)</code></p>
  
  <p>There are 50 <code>licenseNumber/licenseIssuer</code> columns, 15 <code>specialtyName/specialtyState</code> columns, and 15 <code>identifier</code> columns. There is always at least one of each of those, but the remaining 49 or 14 could be <code>null</code>. The first identifier is unique, but is not used as the primary key of the Person in our schema.</p>
  
  <p>My database schema looks like this</p>
  
  <pre><code>People(ID int Identity(1,1))
  Names(ID int, personID int, lName varchar, fName varchar)
  Licenses(ID int, personID int, number varchar, issuer varchar)
  Specialties(ID int, personID int, name varchar, state varchar)
  Identifiers(ID int, personID int, value)
  </code></pre>
  
  <p>The database will already be populated with some <code>People</code> before adding the new ones from the csv.</p>
  
  <p>What is the best way to approach this?</p>
  
  <p>I have tried iterating over the staging table one row at a time with <code>select top 1</code>:</p>
  
  <pre><code>WHILE EXISTS (Select top 1 * from staging)
  BEGIN
      INSERT INTO People Default Values
      SET @LastInsertedID = SCOPE_IDENTITY() -- might use the output clause to get this instead
  
      INSERT INTO Names (personID, lName, fName) 
      SELECT top 1 @LastInsertedID, lName, fName from staging
  
      INSERT INTO Licenses(personID, number, issuer)
      SELECT top 1 @LastInsertedID, licenseNumber1, licenseIssuer1 from staging
  
      IF (select top 1 licenseNumber2 from staging) is not null
      BEGIN
          INSERT INTO Licenses(personID, number, issuer)
          SELECT top 1 @LastInsertedID, licenseNumber2, licenseIssuer2 from staging
      END
  
      -- Repeat the above 49 times, etc...
  
      DELETE top 1 from staging
  END
  </code></pre>
  
  <p>One problem with this approach is that it is prohibitively slow, so I refactored it to use a cursor. This works and is significantly faster, but has me declaring 300+ variables for <code>Fetch INTO</code>.</p>
  
  <p>Is there a set-based approach that would work here? That would be preferable, as I understand that cursors are frowned upon, but I'm not sure how to get the identity from the <code>INSERT</code> into the <code>People</code> table for use as a foreign key in the others without going row-by-row from the staging table.</p>
  
  <p>Also, how could I avoid copy and pasting the insert into the Licenses table? With a cursor approach I could try:</p>
  
  <pre><code>FETCH INTO ...@LicenseNumber1, @LicenseIssuer1, @LicenseNumber2, @LicenseIssuer2...
  INSERT INTO #LicenseTemp (number, issuer) Values
  (@LicenseNumber1, @LicenseIssuer1),
  (@LicenseNumber2, @LicenseIssuer2),
  ... Repeat 48 more times...
  .
  .
  .
  INSERT INTO Licenses(personID, number, issuer)
  SELECT @LastInsertedID, number, issuer
  FROM #LicenseTEMP
  WHERE number is not null
  </code></pre>
  
  <p>There still seems to be some redundant copy and pasting there, though.</p>
  
  <p>To summarize the questions, I'm looking for idiomatic approaches to:</p>
  
  <ol>
  <li>Break up one large staging table into a set of normalized tables, retrieving the Primary Key/identity from one table and using it as the foreign key in the others</li>
  <li>Insert multiple rows into the normalized tables that come from many repeated columns in the staging table with less boilerplate/copy and paste (Licenses and Specialties above)</li>
  </ol>
  
  <p>Short of discreet answers, I'd also be very happy with pointers towards resources and references that could assist me in figuring this out.</p>