Table Save and Restore

Hello .. I have a SQL based system that I inherited. In the course of updating a product, I have had to modify several existing tables and added a few new tables. Now .. we are installed in a few dozen sites with this product and I need to get them the new release. And I need to maintain all their existing data in the files I changed. New files are not an issue. HOW do I save off these changed tables and get them to the clients server AND have the old data mapped into the new table definition??

Thanks

gollnick

You should script all your database changes and then push the scripts out to other locations to be executed.

As soon as you make changes to database objects from inside Management Studio you lose the ability to re-create them easily on another copy of the database.

This script creates a table

Code:

---

Use Health
Go
Drop Table AWCDocuments_T
Go
Create Table AWCDocuments_T
        (DocId                        int        not null identity
        ,DocFolder                varchar(100) not null
        ,DocStatus                varchar(10) not null
        ,DocName                varchar(1000) not null
        ,StoredFileName                varchar(1000) not null
        ,Username                varchar(1000) not null
        ,Notes                        varchar(1000) not null
        ,TDate                        datetime
        ,Constraint PKDocuments
                Primary Key (DocId)
        )
Go
Select * From AWCDocuments_T

---

This script adds a new field at the "second-to-last" position in the record - we always have our TDATE fields as the right-most field. It does this change with data in the table.

Code:

---

Use Funds
Go
EXEC sp_rename 'PenNote_T.TDate','MisMatch','COLUMN'
Go
Alter Table PenNote_T Add TDate datetime null
Go
Update PenNote_T Set TDate=MisMatch
Go
Update PenNote_T Set MisMatch=null
Alter Table PenNote_T Alter Column MisMatch varchar(1) null
Update PenNote_T Set MisMatch=''

---

This script pushes out a function

Code:

---

Use Funds
Go
Drop Function dbo.GetPenNotes_Mismatch_F
Go
Create Function dbo.GetPenNotes_Mismatch_F (@MasId int)
Returns varchar(1000)
As
Begin
Declare @RV varchar(1000)

Select @RV=IsNull(@RV+', ','')+Notes
        From PenNote_T
        Where MasId=@MasId and IsNull(MisMatch,'')='Y'

Return @RV

End
Go

---

These scripts get saved just like source-code to your .Net app. And you can put them under source-control - a real benefit for tracking changes.

Plus you can search them very easily and find references to tables and fields - making future updates and enhancements easier to do.

Not sure I fully understand. Do I need to do all three of these or just one to achieve..

I have a SQL based system that I inherited. In the course of updating a product, I have had to modify several existing tables and added a few new tables. Now .. we are installed in a few dozen sites with this product and I need to get them the new release. And I need to maintain all their existing data in the files I changed. New files are not an issue. HOW do I save off these changed tables and get them to the clients server AND have the old data mapped into the new table definition??

a little dense (came from a main frame environment) gollnick

I came from mainframe world as well.

I gave you 3 samples of scripts.

The first CREATE's a table. You said you have new tables.

The second modifies an existing table adding a field in the middle of all the other fields and keeping the data intact.

The third shows the creation of a function - if you do not use STORED PROCEDURES or SCALAR functions in your DB then ignore that.

Point is that I do not use SSMS to modify table structure.

All SSMS does is create a script internally to modify an existing table - usually coping off the entire table to a temp location and dropping/re-creating that table with new columns and then re-loading it from the temp table.

If you look at my second example I am using a combination of FIELD RENAME and FIELD ADDING techniques to add a new field - shifting an existing field to the right and then re-loading just that one column.

Ahhhhhh.. so write a script like the second one. .. insert my new fields and other items. .. send clients the script and just have them run it. .. wrt? ?

Exactly - and putting this in a .BAT file will automatically run a script (or as many as you want)

Code:

---

sqlcmd -S steve-hp -E -i "Create env_accounts.sql" -o env_accounts.log

---

Look up SQLCMD and you will see what all these switches mean...

btw - that second example is good stuff in that it shows renaming a field and adding "the old named" field at the end and then moving data between the fields and then resetting the datatype of the original field.

I'll give it a shot tomorrow. ..let you know. . Thanks again
gollnick