StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can use this approach:</p> <ul> <li>Put your reference data into XML files, one per table</li> <li>Add XML files with reference data to your database project</li> <li>Use a Post-Deployment script to extract the data from XML and merge it into your tables</li> </ul> <p>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:</p> <pre><code>create table Country ( CountryId uniqueidentifier NOT NULL, CountryCode varchar(2) NOT NULL, CountryName varchar(254) NOT NULL ) </code></pre> <p>Create a new folder called <code>ReferenceData</code> under your database project. It should be a sibling folder of the <code>Schema Objects</code> and <code>Scripts</code>.</p> <p>Add a new XML file called <code>Country.xml</code> to the <code>ReferenceData</code> folder. Populate the file as follows:</p> <pre><code><countries> <country CountryCode="CA" CountryName="Canada"/> <country CountryCode="MX" CountryName="Mexico"/> <country CountryCode="US" CountryName="United States of America"/> </countries> </code></pre> <p>Find <code>Script.PostDeployment.sql</code>, and add the following code to it:</p> <pre><code>DECLARE @h_Country int DECLARE @xmlCountry xml = N' :r ..\..\ReferenceData\Country.xml ' EXEC sp_xml_preparedocument @h_Country OUTPUT, @xmlCountry MERGE 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.CountryName WHEN NOT MATCHED BY TARGET THEN INSERT (CountryId, CountryCode, CountryName) values (newid(), source.CountryCode, source.CountryName) ; </code></pre> <p>I tried this solution only in VS 2008, but it should be agnostic to your development environment.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload