CODEWITHSUNDEEP
sqldatabasenhibernateschema
Neil
Neil

Reputation: 5239

How to update database schema without dropping tables

To update an existing database we include a updates sql file which the installer runs. It looks to see if a table exists and if not then it creates it and the fields. This is an auto generated file from some nHibernate functionality.

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[TableA]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
create table

This works fine for creating a new database but during development when the contents of the tables are changing this means the testing dept have to drop their database and start again from scratch. This is very time consuming for them as they then have to populate the tables with new data again. I have a pretty limited knowledge of sql and my searches for a solution have so far proven fruitless.

Is there a way to have the tables update regardless of whether they exist and not have to drop the schema and start again?

Many thanks

Upvotes: 2

Views: 3984

Answers (1)

Daryl Teo
Daryl Teo

Reputation: 5495

What you're looking for is the ALTER TABLE, ADD statement.

http://msdn.microsoft.com/en-us/library/ms190273.aspx

ALTER TABLE Comments ADD newColumn INT

As an example.

The question, then, becomes how do you do this for a whole database? What you will need is to have a SQL Delta tool. (Here's an example of one http://www.sqldelta.com/ but there are others!)

This tool will then generate an update script that you can run on your old database to bring it up to sync with your latest database.

Upvotes: 1

Related Questions