Add Reference Data to Tables When You Deploy the Database

Question

Add Reference Data to Tables When You Deploy the Database

Abhishek Chaturvedi

SSC Eights!

Points: 970
More actions
April 21, 2011 at 9:06 pm

#96200

I am working on the database deployment scripts and below is the script I am wanting to create.
if not exists (select Description from dbo.AppealType where Description = 'ICC')
begin
insert dbo.test([Description], [GroupType], [Sortorder], [CreatedBy], [CreatedDate], [LastUpdatedBy],[LastUpdatedDate])
VALUES ('ICC', 2, 2, suser_sname(), getdate(), suser_sname(), getdate())
end
My problem is that there over 60 reference tables and over 20,000 rows to be inserted in the post deployment script.
I wanted to know if there is any tool or way to automate this.
Abhishek

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323516 More actions · Answer 1

don't deploy a script ;give the end user a complete backup with all tables and default data already in place; it's faster, easier and not prone to script errors

it's also a best practice in my opinion as well

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Abhishek Chaturvedi SSC Eights! Points: 970 More actions · Answer 2

I wish I could do that but I was ordered by the management and the senior DBA to have a post deployment script in place.Any work arounds? or non recommended methods?

Lowell SSC Guru Points: 323516 More actions · Answer 3

the SSMS has an option to script data as wll as the table definitions; i've used it before

but it's weird when i went to Tools>>Options ...SQL Server Object Explorer>>Scripting i couldn't find the script data selection.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 4

You might want to take a look at Red Gate SQL Data Compare. It's a great tool for moving data between two databases. You can automate it too so that you can move data as part of your deployment processes. I cover a lot of this in the Team-based Development book chapters on deploying databases.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Abhishek Chaturvedi SSC Eights! Points: 970 More actions · Answer 5

I will take a look at the data compare tool. I have heard that red gate is pretty good.

Steve Jones - SSC Editor SSC Guru Points: 735611 More actions · Answer 6

I work for Red Gate, and Data Compare makes this easy if you are moving lots of data.

However the other thing you can do is export the data with bcp and use a deployment script to insert the data into the next server.

Abhishek Chaturvedi SSC Eights! Points: 970 More actions · Answer 7

ok. Thanks for your advice. I will look into the Red Gate software

WayneS SSC Guru Points: 95450 More actions · Answer 8

A few other things you can do with Redgate tools: you can use SQL Source Control, and source control the data in those reference tables. SQL Compare will work with SQL Source Control to generate the export scripts also.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2