Generate seed data in Visual Studio 2010 Database project

I have a database and a database pro开发者_运维问答ject in Visual studio 2010. I inferred the schema in the database project successfully but I also need to import somehow the data in a few tables (Country, State, UserType etc.) that are reference tables and not really data tables.

Is there a way?

The only way I found so far is generating data scripts in SQL Server Management Studio and placing this script in the post-deployment script file in the database project.

Any simpler way?

Try the Static Data Script Generator for SQL Server. It automates those Post-Deployment scripts for you in the correct format. It is a free Google Code hosted project and we have found it useful for scripting our static data (also over the top of existing data for updates as well).

this is pretty much how I've done it before.

I would just make sure that each statement in you script look something like:

IF (EXISTS(SELECT * FROM Country WHERE CountryId = 1))
    UPDATE MyTable SET Name = 'UK' WHERE CountryId = 1 AND Name != 'UK'
ELSE
    INSERT INTO MyTable (CountryId, Name) VALUES (1, 'UK')

This means that each time you deploy the database your core reference data will be inserted or updated, whichever is most appropriate, and you can modify these scripts to create new reference data for newer versions of the database.

You could use a T4 template to generate these scripts - I've done something similar in the past.

you can also use the Merge INTO statement for updating/deleting/inserting seed data into the table in your post deployment script.I have tried and its works for me here is the simple example:

*/ print 'Inserting seed data for seedingTable'

MERGE INTO seedingTable AS Target 
USING (VALUES   (1, N'Pakistan', N'Babar Azam', N'Asia',N'1'),
(2, N'England',  N'Nasir Hussain', N'Wales',N'2'), 
(3, N'Newzeland', N'Stepn Flemming', N'Australia',N'4'), 
(4, N'India', N'Virat Koli', N'Asia',N'3'),  
(5, N'Bangladash', N'Saeed', N'Asia',N'8'),   
(6, N'Srilanka', N'Sangakara', N'Asia',N'7') ) 
AS Source (Id, Cric_name,captain,region,[T20-Rank]) ON Target.Id = Source.Id

-- update matched rows 
   WHEN MATCHED THEN 
   UPDATE SET Cric_name = Source.Cric_name, Captain = Source.Captain, Region=source.Region, [T20-Rank]=source.[T20-Rank]

-- insert new rows 
   WHEN NOT MATCHED BY TARGET THEN 
   INSERT (Id, Cric_name,captain,region,[T20-Rank]) 
   VALUES (Id, Cric_name,captain,region,[T20-Rank])
-- delete rows that are in the target but not the source
   WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

By Using postdeployment.script and its executed perfectly with new table and seed data into it below is the script,after that I want to add new column and insert data into it how i i can do this

 insert into seedingTable (Id, Cric_name, captain, region,[T20-Rank])
 select 1, N'Pakistan', N'Babar Azam', N'Asia',N'1' 
 where not exists
 (select 1 from dbo.seedingTable where id=1)     
 go
 insert into seedingTable (Id, Cric_name, captain, region,[T20-Rank]) select 2,
 N'England', N'Nasir Hussain', N'Wales',N'3' 
 where not exists
 (select 1 from dbo.seedingTable where id=2)

Let me know above script will run every time when deploying database by using azure pipeline. how to update data.