title: Best practice to populate static data using a Visual Studio 2010 database project?
body: <p>How do you populate your database with static, source-controlled data using a Visual Studio database project? I have tried all three strategies below, finding each one to be progressively better than the last. I am using but not completely satisfied with strategy 3. Do you have an another alternative?</p>
  <ol>
  <li>Place insert scripts in the &quot;Data Generation Plans&quot; folder. Reference the scripts in the &quot;Script.PostDeployment.sql&quot; file to include them in the deployment process.</li>
  </ol>
  <p>-- advantage: straight-forward<br />
  -- drawback:  slooooooow<br />
  -- drawback:  subsequent deploys must first delete static data or check for non-existence of data =&gt; inefficient</p>
  <ol>
  <li>Insert the data into the database the first time using whatever method is most convenient (e.g. could be the SSMS edit table feature). Extract that data using the bcp command line utility to create a bunch of data files and add them to your project. Create a script referenced in the &quot;Scripts.PostDeployment.sql&quot; file that executes a &quot;bulk insert&quot; statement for each data file.</li>
  </ol>
  <p>-- advantage: much faster than insert statements<br />
  -- advantage: can leverage SSMS edit table feature<br />
  -- drawback:  each bulk insert statement requires a fully qualified file name to the data file so if the data files are located on my machine at &quot;C:\Projects\Dev\Source\foo.dat&quot; then the remote dev machine must also have them at that location or the bulk insert statement fails<br />
  -- drawback: must delete existing static data before executing bulk insert statements on subsequent deploys</p>
  <ol>
  <li>Create temporary tables during deployment to hold the static data and use the sql merge statement to synchronize these tables with the target tables. See <a href="https://web.archive.org/web/20151218231845/http://blogs.msdn.com:80/b/bahill/archive/2009/04/01/maintaining-and-synchronizing-your-reference-data.aspx" rel="nofollow noreferrer">either</a> of <a href="https://web.archive.org/web/20110812013611/http://leon.mvps.org:80/StaticData/" rel="nofollow noreferrer">these</a> blog posts.</li>
  </ol>
  <p>-- advantage: seems like sql merge has the perfect semantics for the problem<br />
  -- drawback:  the logic for this strategy is repeated in each file
  -- drawback:  table definitions are repeated as temporary tables in the sql merge files</p>
  <p>Is there a superior alternative strategy? I gave up on strategy 1 because it was too slow. I dislike strategy 2 due to the fully qualified file name issue. I am satisfied but not thrilled by strategy 3. Is there a best practice?</p>