3

I have a database and a database project 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?

Andrei Rînea
  • 20,288
  • 17
  • 117
  • 166

4 Answers4

2

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).

Rebecca
  • 13,914
  • 10
  • 95
  • 136
  • Static Data Script Generator blows up the first time you run it because you have to manually edit the config. Then, it blows up on some primary key violation unhandled exception on the tables I was trying to generate the scripts for. YMMV. – Peter Ritchie Nov 28 '11 at 21:15
  • 1
    You do need to use some sense with it. Yes it does blow up the first time as the config needs to be set so it knows what database you want. As for the second, it was likely the same issue I found. The script doesn't examine foreign keys and has no way to know how to order inserts, so it just generates the index script in the order it processes them. You need to manually organise the order they are run in to avoid foreign key problems. Primary key violations likely means you are trying to run the data against a database that already has the data in it. In anycase, thanks a heap Junto! – Frater Apr 18 '12 at 01:41
1

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.

Mike Goatly
  • 7,380
  • 2
  • 32
  • 33
  • Yeah, that's pretty much the same like what I've done. I was hoping for a UI hint in the 'Import schema' to also import data or sth... but I guess it's just not available.. – Andrei Rînea Apr 02 '11 at 14:17
  • Nope, not that I'm aware of. Datadude seems to be primarily focused on schema, rather than data. – Mike Goatly Apr 02 '11 at 15:15
  • Thanks... it seems that's the way that I'll maintain the reference data :) – Andrei Rînea Apr 03 '11 at 20:34
  • Here at Red Gate we're planning on supporting the database project, which should mean that SQL Source Control's features, such as support for static data, would be available on top of the database project back end. You can vote up this feature at: http://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/1010465-work-with-a-visual-studio-2010-database-project?ref=title – David Atkinson Apr 10 '11 at 19:49
  • 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) – Saad Awan Apr 06 '20 at 05:52
1

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;
Saad Awan
  • 566
  • 2
  • 9
  • 23
0

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.

Saad Awan
  • 566
  • 2
  • 9
  • 23