Solutions for simultaneous updating of tables in the database

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.