Best practice to populate static data using a Visual Studio 2010 database project?

Question

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?

1. Place insert scripts in the "Data Generation Plans" folder. Reference the scripts in the "Script.PostDeployment.sql" file to include them in the deployment process.

   -- advantage: straight-forward
   -- drawback: slooooooow
   -- drawback: subsequent deploys must first delete static data or check for non-existence of data => inefficient

2. 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 "Scripts.PostDeployment.sql" file that executes a "bulk insert" statement for each data file.

   -- advantage: much faster than insert statements
   -- advantage: can leverage SSMS edit table feature
   -- 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 "C:\Projects\Dev\Source\foo.dat" then the remote dev machine must also have them at that location or the bulk insert statement fails
   -- drawback: must delete existing static data before executing bulk insert statements on subsequent deploys

3. 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 either of these blog posts.

   -- advantage: seems like sql merge has the perfect semantics for the problem
   -- drawback: the logic for this strategy is repeated in each file -- drawback: table definitions are repeated as temporary tables in the sql merge files

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?

Answer 1

In your insert.sql script, you can put a GUID in the [__RefactorLog] table (which is a system table used by deployment) and check if this GUID exist before inserting your data like this :

:setvar SOMEID "784B2FC9-2B1E-5798-8478-24EE856E62AE" //create guid with Tools\CreateGuid in VS2010

IF NOT EXISTS (SELECT [OperationKey] FROM [dbo].[__RefactorLog] where [OperationKey] = '$(SOMEID )')

BEGIN

...

INSERT INTO [dbo].[__RefactorLog] ([OperationKey] ) values( '$(SOMEID )' )

END

Then you insert data only if not exist or if you want to (by changing the Guid).