It should be common to update Tables in databases at the same time, because most of the time we develop database systems
Many tables are associated. Therefore, when updating a table, you must perform operations on other tables at the same time.
Synchronization addition, synchronization deletion, and Synchronization Update. Generally, triggers are used. These operations do not need to be modified on the table structure.
But the current problem is not like this. Let's look at the problem below:
Question:Table 1 has 88703 records and does not have the same records. It has a unique primary key. Table 2 has 1992 records,
Table2 records are from Table1, so add a unique ID that can identify each record in Table1 and Table2, respectively,
And the ID column must be in the first column.
Problem Analysis:Assume that Table1 and Table2 have four columns: A, B, C, and D, where A is the primary key. Now you need to add a column in these two tables.
ID, so that the relationship between Table1 and Table2 remains unchanged after the addition, that is, the ID of Table2 is also from table1.
We can use a cursor to solve the efficiency problem. It is not difficult to add a column and the first column.
Solution.
Solution:First, add a column ID to table 1 and table 2.
Alter Table Table1 Add ID Int Identity ( 1 , 1 )
Alter Table Table 2 Add ID Int
Set it to the first column
Sp_configure ' Allow updates ' , 1
Reconfigure With Override
Go
Update Syscolumns Set Colid = Colid + 1
Where ID = Object_id ( ' Table1 ' )
Update Syscolumns Set Colid = 1
Where ID = Object_id ( ' Table1 ' ) And Name = ' ID '
Go
Sp_configure ' Allow updates ' , 0
Reconfigure With Override
The setting method of Table2 is the same. You only need to replace Table1 with Table2. In fact, other methods can be implemented, such as using a temporary table.
And so on. If you are interested, try again. The following is how to update Table2 so that the ID in Table2 and the ID in Table1
Synchronization, which is implemented with a cursor:
Declare Mycursor Cursor For
Select Table1.id, table2.id, table1.a, table2.a From Table 2
Left Join Table1 On Table1.a = Table2.a
Open Mycursorfetch Next From Mycursor
While @ Fetch_status = 0
Begin
Update Table 2 Set Table2.id = Table1.id From Table1
Where Table2.a = Table1.a
Fetch Next From Mycursor
End
Close Mycursor
Deallocate Mycursor
Now the problem has been solved, and there may be other better methods, but I should say that the efficiency is relatively high.