Create Database Upgrade script

Recently, as the company took charge of the database, no one did some database tasks, and the leader found me. This task is responsible for the upgrade script of the new version of the database. The difficulty is that I am not very familiar with the structure of the data table, and I am not responsible for the functions of the new version. In addition, the structure of the data table is greatly changed, ensure that the user's original data cannot be lost. There are still data in more than 200 tables. If it is done manually, no one will blame. So the search tool first starts with the familiar powerdesigner, first generates the models of the New and Old databases with reverse engineering, then click Database-> modify Database in the menu, and then follow the steps, no difficulty. After completion, a very long SQL statement is generated. Because it is generated by a machine and looks very troublesome, I am so worried that I can try using backup and upgrade. After the execution, a bunch of errors are reported. Check whether the table is changed, but the data is lost, and you are not sure if the table is upgraded. Alas, this does not work for situations where the table changes much or the original data is large.

Note that the use alter statements when possible option in the figure is too ambiguous. I tried it. Most of them, as long as the changes to the table are complex, they began to become lazy. They didn't need to alter the table, directly create and then import the data of the original table. Not to mention efficiency, it is not good if data is lost.

If your tables and data change a little, PD is also a good method. After all, it is convenient and efficient. If not, the following describes a good tool, SQL delta, which is used to compare the database.

Go to the system, set the database to be upgraded and the database before the upgrade, and click Compare databases to compare the table structure.

You can find that the field changes are clear, and there are many, few, and change types. We recommend that you do not delete unnecessary fields during the upgrade, but add or modify them only.

You can see the upgrade script recommended by the tool on the right side. Because it is a table and a table, although the workload is heavy, it is more reassuring and the script is readable.

Note that the tool still uses the following methods for table changes (first create a temporary table, import the original table data, and then delete the original table, change the name of a temporary table to the name of the original table)

If @ trancount = 1 <br/> Create Table [DBO]. [tmp_ticket] (<br/> [ticket_id] [varchar] (32) Collate does not null, <br/> [ticket_num] [varchar] (50) Collate does not null, <br/> [ticket_subject] [varchar] (200) Collate SQL _latin1_general_cp1_ci_as null, <br/> [ticket_desc] [varchar] (2500) Collate limit null, <br/> [Priority] [char] (3) Collate SQL _latin1_general_cp1_ci_as not null, <br/> [severity] [char] (3) Collate SQL _latin1_general_cp1_ci_as not null, <br/> [status_id] [varchar] (32) Collate SQL _latin1_general_cp1_ci_as not null, <br/> [channel_id] [varchar] (32) Collate 1_null, <br/> [machine_id] [varchar] (32) Collate SQL _latin1_general_cp1_ci_as not null, <br/> [requester_contact_id] [varchar] (32) Collate limit null, <br/> [owner_id] [varchar] (32) Collate SQL _latin1_general_cp1_ci_as not null, <br/> [assignee_id] [varchar] (32) Collate limit null, <br/> [escalated_to] [varchar] (32) Collate SQL _latin1_general_cp1_ci_as null, <br/> [item_id] [varchar] (32) Collate does not null, <br/> [resolution_id] [varchar] (32) Collate SQL _latin1_general_cp1_ci_as null, <br/> [followup_flag] [char] (1) Collate does not null, <br/> [report_time] [datetime] not null, <br/> [create_time] [datetime] not null, <br/> [dispatch_time] [datetime] Null, <br/> [assign_time] [datetime] Null, <br/> [confirm_time] [datetime] Null, <br/> [response_time] [datetime] Null, <br/> [start_working_time] [datetime] Null, <br/> [sla_resp_expected] [datetime] Null, <br/> [sla_resp_hours] [decimal] (4, 0) null, <br/> [response_time_expected] [datetime] Null, <br/> [resp_time_actual_val] [decimal] (10, 4) null, <br/> [resp_timeline_type] [char] (3) Collate SQL _latin1_general_cp1_ci_as null, <br/> [chronic_action] [char] (3) Collate limit null, <br/> [recover_time] [datetime] Null, <br/> [fixed_time] [datetime] Null, <br/> [sla_fixed_expected] [datetime] Null, <br/> [fixed_time_expected] [datetime] Null, <br/> [fixed_time_actual_val] [decimal] (10, 4) null, <br/> [fixed_timeline_type] [char] (3) Collate SQL _latin1_general_cp1_ci_as null, <br/> [suspend_time] [datetime] Null, <br/> [closed_time] [datetime] Null, <br/> [contract_flag] [char] (1) Collate SQL _latin1_general_cp1_ci_as not null, <br/> [parent_id] [varchar] (32) Collate SQL _latin1_general_cp1_ci_as null <br/> [parent_flag] [char] (1) Collate SQL _latin1_general_cp1_ci_as null, <br/> [group_id] [varchar] (32) Collate SQL _latin1_general_cp1_ci_as null, <br/> [owner_group_id] [varchar] (32) Collate limit null, <br/> [previus_group_id] [varchar] (32) Collate SQL _latin1_general_cp1_ci_as null, <br/> [sp_id] [varchar] (32) Collate limit null, <br/> [customer_id] [varchar] (32) Collate SQL _latin1_general_cp1_ci_as null, <br/> [closed_by] [varchar] (32) Collate limit null, <br/> [assignment_flag] [char] (1) Collate SQL _latin1_general_cp1_ci_as null, <br/> [is_auto_created] [char] (1) Collate limit null, <br/> [is_auto_fixed] [char] (1) Collate SQL _latin1_general_cp1_ci_as null, <br/> [auto_fix_enable] [char] (1) collate SQL _latin1_general_cp1_ci_as not null constraint [df1_ticket1_auto_fix1_0f624af8] default ('1'), <br/> [auto_response_enable] [char] (1) collate SQL _latin1_general_cp1_ci_as not null constraint [constraint] default ('1'), <br/> [reopen_times] [int] Null, <br/> [response_time_manual] [datetime] Null, <br/> [fixed_time_manual] [datetime] Null, <br/> [history_id] [varchar] (32) Collate SQL _latin1_general_cp1_ci_as null, <br/> [last_updated] [datetime] not null, <br/> [fix_exclude_time_val] [decimal] (10, 4) null, <br/> [ticket_domntime] [decimal] (10, 4) null, <br/> [extenal_reference_no] [varchar] (32) Collate SQL _latin1_general_cp1_ci_as null <br/>) <br/> go <br/> If @ error <> 0 <br/> If @ trancount = 1 rollback transaction <br/> go <br/> If @@ trancount = 1 <br/> insert into [DBO]. [tmp_ticket] ([ticket_id], [ticket_num], [ticket_subject], [Priority], [Priority], [severity], [status_id], [channel_id], [machine_id], [shard], [owner_id], [assignee_id], [escalated_to], [item_id], [resolution_id], [followup_flag], [report_time], [create_time], [dispatch_time], [assign_time], [confirm_time], [response_time], [start_working_time], [interval], [interval], [interval], [interval], [resp_timeline_type], [chronic_action], [recover_time], [fixed_time], [interval], [fixed_time_expected], [interval], [interval], [suspend_time], [closed_time], [contract_flag], [parent_id], [parent_flag], [group_id], [owner_group_id], [previous_group_id], [sp_id], [customer_id], [closed_by], [delimiter], [is_auto_created], [is_auto_fixed] [auto_fix_enable], [reopen_times], [response_time_manual], [fixed_time_manual], [history_id], [last_updated], [timer], [ticket_domntime], [timer]) <br/> select [ticket_id], [ticket_num], [ticket_subject], [ticket_desc], [Priority], [severity], [status_id], [channel_id], [machine_id], [region], [owner_id], [assignee_id], [escalated_to], [item_id], [resolution_id], [followup_flag], [report_time], [create_time], [dispatch_time], [assign_time], [confirm_time], [response_time], [start_working_time], [interval], [interval], [interval], [interval], [resp_timeline_type], null, [recover_time], [fixed_time], [scheduled], [fixed_time_expected], [scheduled], [fixed_timeline_type], [suspend_time], [closed_time], [contract_flag], [parent_id], [parent_flag], [group_id], [owner_group_id], [previous_group_id], [sp_id], [customer_id], [closed_by], [secret], [is_auto_created], [is_auto_fixed], [auto_fix_enable], null, [response_time_manual], [fixed_time_manual], [history_id], [last_updated], null, null <br/> from [DBO]. [ticket] <br/> go <br/> If @ error <> 0 <br/> If @ trancount = 1 rollback transaction <br/> go <br/> if @ trancount = 1 <br/> drop table [DBO]. [ticket] <br/> go <br/> sp_rename n' [DBO]. [tmp_ticket] ', n' ticket'

Next, compare the data in the table, press data in the menu bar, and then click wizard.

Select the table for which you want to compare data. Generally, some configuration tables are used. Do not lose your data.

You can observe A and B to know the data changes. If you want to reflect the changes in the upgrade script, you can remove the check if you do not want them to take effect. Finally, click Sync to enter and save it. The script is generated. The DDL and DML of the Database Upgrade have been implemented. If the database is generated properly, you need to perform multiple tests. After all, the upgrade is risky. Please back up the database before the upgrade.