CODEWITHSUNDEEP
sql-server
Leon
Leon

Reputation: 31

How to update tables' structures keeping current data

I have a C# application that uses tables in a SQL Server 2008 database (runs on standalone pc with local SQL Server).

Initially I install database on this pc with some initial data (there are some tables that application uses and the user doesn't touch).

The question is - how can I upgrade this database after user created some new data without harming it (I continue developing on another pc and can add some new tables or stored procedures or add some columns to existing tables).

I mean that when I release new version I bring new version of database on backup file (or anything else?) that holds new structure and those initial tables and I need to upgrade existing database without loosing current data.

Thanks in advance!

Upvotes: 3

Views: 5622

Answers (4)

B. P.
B. P.

Reputation: 1

Really hate doing this without the specifics, but ...

In general:

  1. Rename current table to temporary table
  2. Create table with new configuration
  3. Select from temporary table INTO new table
  4. If everything worked, delete temporary table

All this can be done in one script. I've seen it done.

Alternatively, you might want to consider this: SQL Server Management Studio - how to change a field type without dropping table

Upvotes: 0

marc_s
marc_s

Reputation: 755073

You need to upgrade your existing database structure using change scripts using commands like ALTER TABLE and so on.

There's basically two ways to do this:

  • you can use a "diff" based tool like Red-Gate SQL Compare to determine the differences between your database version x and version (x+1). That tool will create a change script for you, which you can then execute on the target machine to bring your database up to the new structure

  • or you can write those change scripts manually and package them up and execute them on the target machine

Either way: you need to keep track of your database versions, and you need to use extreme caution not to cause any data loss with any scripts (like dropping a table or something)

Upvotes: 5

jamesmortensen
jamesmortensen

Reputation: 34048

You can also use Foreign keys to link rows from one table with the rows in another table. This might help keep your tables smaller and your data more segregated.

Upvotes: 0

escargot agile
escargot agile

Reputation: 22389

Adding new tables shouldn't hurt existing tables. But when adding columns, you should be careful to fill the values for these columns for existing rows (especially if the columns have a not-null constraint on them - in this case you must use the DEFAULT clause). You can achieve this using the DEFAULT clause and by running a special SQL query after the column is created in order to set the new column's value in existing rows.

Upvotes: 1

Related Questions