In Visual Studio, you can create SQL Database Projects to handle deploying / updating databases. When deploying, it will modify the target database to match the state of the source database. Meaning: you don’t set up a script to create a table. Rather, the database project knows the schema of the table, and the deployment will determine what differences exist and alter the target table as necessary, adding fields, constraints, etc.
There are some of walkthroughs available from MS:
There is also a codeplex project with additional documentation:
Following are a couple other notes, in no particular order:
The deployment will update table schemas and objects, but not data. You can certainly add a post-deployment script to insert data, but the script should be written in such a way so that it can be run multiple times without issue.
Bill Gibson has a good post on this with sample code:
To add a post-deployment script, r-click on the project à add à Script.
To change the type or disable a script, select it in the solution explorer, and change the “BuildAction”.
Post-Deployment scripts are run after the schema changes have been made, and before the check constraints have been re-enabled.
In the publish dialog, after filling in the form appropriately, and before hitting publish, click on “create profile”. The next time you run the publish, just click “load profile” and select the file (the file name should be projectname.publish.
One of the advanced options is “Generate Smart Defaults”. For example, if an int field was added with no default value, the smart default option will set that field to zero. If that field is a foreign key, it will still be set to zero and re-enabling the check constraint will fail, so set proper field defaults.