title: How to use BULK INSERT when rows depend on foreign keys values?
body: <p>My question is related to <a href="https://serverfault.com/questions/337600/any-known-sql-server-2008-management-studio-memory-leak-issues">this one I asked on ServerFault</a>.</p>
  
  <p>Based on this, I've considered the use of <a href="http://msdn.microsoft.com/en-us/library/ms188365.aspx" rel="nofollow noreferrer"><code>BULK INSERT</code></a>. I now understand that I have to prepare myself a file for each entities I want to save into the database. No matter what, I still wonder whether this BULK INSERT will avoid the memory issue on my system as described in the referenced question on ServerFault.</p>
  
  <p>As for the Streets table, it's quite simple! I have only two cities and five sectors to care about as the foreign keys. But then, how about the Addresses? The Addresses table is structured like this:</p>
  
  <pre><code>AddressId int not null identity(1,1) primary key
  StreetNumber int null
  NumberSuffix_Value int not null DEFAULT 0
  StreetId int null references Streets (StreetId)
  CityId int not null references Cities (CityId)
  SectorId int null references Sectors (SectorId)
  </code></pre>
  
  <p>As I said on ServerFault, I have about 35,000 addresses to insert. Shall I memorize all the IDs? =P</p>
  
  <p>And then, I now have the citizen people to insert who have an association with the addresses.</p>
  
  <pre><code>PersonId int not null indentity(1,1) primary key
  Surname nvarchar not null
  FirstName nvarchar not null
  IsActive bit
  AddressId int null references Addresses (AddressId)
  </code></pre>
  
  <p>The only thing I can think of is to force the IDs to static values, but then, I lose any flexibility that I had with my former approach with the <code>INSERT..SELECT</code> stategy.</p>
  
  <p>What are then my options?</p>
  
  <ol>
  <li><p>I force the IDs to be always the same, then I have to <code>SET IDENTITY_INSERT ON</code> so that I can force the values into the table, this way I always have the same IDs for each of my rows just as suggested <a href="https://stackoverflow.com/questions/5650978/bulk-insert-into-table-with-foreign-key">here</a>.</p></li>
  <li><p>How to BULK INSERT with foreign keys? I can't get any docs on this anywhere. =(</p></li>
  </ol>
  
  <p>Thanks for your kind assistance!</p>
  
  <blockquote>
    <p><strong>EDIT</strong></p>
    
    <p>I edited in order to include the <code>BULK INSERT</code> SQL instruction that finally made it for me!</p>
  </blockquote>
  
  <p>I had my Excel workbook ready with the information I needed to insert. So, I simply created a few supplemental worksheet and began to write formulas in order to "import" the information data to these new sheets. I had one for each of my entities.</p>
  
  <ol>
  <li>Streets;</li>
  <li>Addresses;</li>
  <li>Citizens.</li>
  </ol>
  
  <p>As for the two other entities, it wasn't worthy to bulk insert them, as I had only two cities and five sectors (cities subdivisions) to insert. Once the both the cities and sectors inserted, I noted their respective IDs and began to ready my record sets for bulk insert. Using the power of Excel to compute the values and to "import" the foreign keys was a charm of itself, by the way. Afterwards, I have saved each of the worksheets to a separated CSV file. My records were then ready to bulked.</p>
  
  <pre><code>USE [DatabaseName]
  GO
  
  delete from Citizens
  delete from Addresses
  delete from Streets
  
  BULK INSERT Streets
      FROM N'C:\SomeFolder\SomeSubfolder\Streets.csv'
      WITH (
          FIRSTROW = 2
          , KEEPIDENTITY
          , FIELDTERMINATOR = N','
          , ROWTERMINATOR = N'\n'
          , CODEPAGE = N'ACP'
      )
  GO
  </code></pre>
  
  <blockquote>
    <ul>
    <li><p>FIRSTROW</p>
    
    <p>Indicates the row number at which to begin the insert. In my situation, my CSVs contained the column headers, so the second row was the one to begin with. Aside, one could possibly want to start anywhere in his file, let's say the 15th row.</p></li>
    <li><p>KEEPIDENTITY</p>
    
    <p>Allows one to bulk-insert specified in-file entity IDs even though the table has an identity column. This parameter is the same as <code>SET INDENTITY_INSERT my_table ON</code> before a row insert when you wish to insert with a precise id.</p></li>
    </ul>
  </blockquote>
  
  <p>As for the other parameters, they speak by themselves.</p>
  
  <p>Now that this is explained, the same code was repeated for each of the two remaining entities to insert Addresses and Citizens. And because the <code>KEEPIDENTITY</code> was specified, all of my foreign keys remained still, though my primary keys were set as identities in SQL Server.</p>
  
  <p>Only a few tweaks though, just the exact same thing as <code>marc_s</code> said in his answer, just import your data as fast as you can into a staging table with no restriction at all. This way, you're gonna make your life much easier, while following good practices nevertheless. =)</p>