SQL statement used to operate the master-slave relationship table in sqlserver

A typical method is to update or add data fields in the master table. For a slave table, all related records are deleted first and then inserted. That is to say, the data from the table must be deleted and inserted again each time it is saved. This is a good method. After using database transactions, there will be no data inconsistency caused by deletion and no insertion.
OK. The principle is as follows. How can we operate it in actual operations? Some people have completed the above requirements through multiple database operations in the program. This method may result in data inconsistency because it cannot guarantee an exception between two data statement operations. Then we can merge it into a stored procedure. Complicated parameters and post-processing make the stored procedure more difficult. Let me talk about this process based on actual applications. Application Scenario: in student management, add a student record and add the course corresponding to the student. That is to say, each time you update and add student records, the corresponding course also needs to change.
Then this process is in line with the above logic.
The general parameter transfer is not detailed here. It focuses on how to handle the problem after the subject information is transferred. Assume that the Tb_StudentSubject table contains the StudentId and SubjectId fields. The Code is as follows:
Copy codeThe Code is as follows:
Create proc SaveInfo
(
--- The parameter list is no longer written one by one
@ Subject tlist varchar (400 ))
As
Begin tran
-- Adding or modifying a master table is omitted.
DECLARE @ subjectID int,
@ SubjectIDsLen int, -- Subject Length
@ SubjectIDLen int, -- length of a single subject
@ NStart INT, -- Record the start position of the query
@ NLast INT -- Record ',' location
Select @ subjectIDsLen = len (@ SubjectIDs), @ nStart = 1, @ nLast = 0
DELETE Tb_StudentSubject WHERE StudentId = @ sId -- DELETE all related records
IF (@ subjectIDsLen> 0)
BEGIN
While (@ nStart <@ subjectIDsLen)
BEGIN
Set @ nLast = charindex (',', @ SubjectIDs, @ nStart) -- Record the location of the comma from the position @ nStart
Set @ subjectIDLen = @ nLast-@ nStart -- Record the length of each subjectID
Set @ subjectID = substring (@ SubjectIDs, @ nStart, @ subjectIDLen) -- get a subjectID
Insert Into Tb_StudentSubject (StudentId, SubjectID) values (@ sId, @ subjectID)
Set @ nStart = @ nLast + 1 -- set the start position of the next search
END
END
If @ Error = 0
Commit transaction
Else
Rollback transaction
End


In this Code, the subject list is separated by ",", and the above stored procedure is annotated, focusing on the built-in functions of the two systems.
CHARINDEX (expression1, expression2 [, start_location])
Expression1: the sequence of characters to be searched.
Expression2: Usually a column for the specified sequence search
Start_location: Start to search for the character position of expression1 in expression2. If start_location is not specified, it is a negative number or zero
Another function is: SUBSTRING (expression, start, length) to extract the specified length string from the specified position from the specified data source string.
Expression: a string, binary string, and data source string to be operated
Start: start position of the part to be intercepted. Note that this method and C # are different. in the database, the strat starts from 1 and the program starts from 0.
Length: the length of the intercepted content.
The other one is len (expres), which is easier to obtain the length of a specified character.
With the above code, you can implement a complete storage process to process Master/Slave table data. This ensures data consistency and ease of operation, and is the same and common.
Note: In tables involving similar master-slave relationship, we recommend that you do not set an auto-numbered primary key in the table. because of frequent deletion and addition, the primary key is constantly increasing, it does not make sense to use this primary key.
The above is my personal opinion. I hope you can share more ideas.