How do you store static data in your SQL Server Database Project in VS 2012

You can use this approach:

• Put your reference data into XML files, one per table
• Add XML files with reference data to your database project
• Use a Post-Deployment script to extract the data from XML and merge it into your tables

Here is a more detailed description of each step, illustrated with an example. Let's say that you need to initialize a table of countries that has this structure:

create table Country (    CountryId uniqueidentifier NOT NULL,    CountryCode varchar(2) NOT NULL,    CountryName varchar(254) NOT NULL)

Create a new folder called ReferenceData under your database project. It should be a sibling folder of the Schema Objects and Scripts.

Add a new XML file called Country.xml to the ReferenceData folder. Populate the file as follows:

<countries>    <country CountryCode="CA" CountryName="Canada"/>    <country CountryCode="MX" CountryName="Mexico"/>    <country CountryCode="US" CountryName="United States of America"/></countries>

Find Script.PostDeployment.sql, and add the following code to it:

DECLARE @h_Country intDECLARE @xmlCountry xml = N':r ..\..\ReferenceData\Country.xml'EXEC sp_xml_preparedocument @h_Country OUTPUT, @xmlCountryMERGE Country AS target USING (    SELECT c.CountryCode, c.CountryName    FROM OPENXML(@h_Country, '/countries/country', 1)    WITH (CountryCode varchar(2), CountryName varchar(254)) as c) AS source (CountryCode, CountryName)ON (source.CountryCode = target.CountryCode)WHEN MATCHED THEN    UPDATE SET CountryName = source.CountryNameWHEN NOT MATCHED BY TARGET THEN    INSERT (CountryId, CountryCode, CountryName) values (newid(), source.CountryCode, source.CountryName);

I tried this solution only in VS 2008, but it should be agnostic to your development environment.