Everything is in the title, this tutorial uses for example the insertion of data into a table according to the result of a query based on a linked table
For our example let’s take a simplified database containing 2 tables.
The first: USERS (ID_USER, NAME, PW, STATE) manages the users. The second GROUPS (ID_USER, NOM_GROUP) manages the groups assigned to the users.
CONTENT OF TABLES
| Table USERS | |||
| ID_USER | NAME | PW | STATE |
| 1 | MARK | Xb4545 | FATIGUE |
| 2 | MOLLY | FfAA45A | MOBILITY |
| 3 | CANDY | RT4ACZ | FATIGUE |
| Table GROUPS | |||
| ID_USER | NAME_GROUP | ||
| 1 | ADMIN | ||
| 1 | CHIEF | ||
| 2 | ADVISOR | ||
| 2 | CHIEF | ||
| 3 | FIREFIGHTER | ||
On this table I want all users whose STATUS is “FATIGUE” to belong to a new group “NEED HOLIDAYS”. To do it I will use a CURSOR which will recover all the ID_USER of the users (table USERS) whose field STATE = “FATIGUE” and for each one of these ID_USER one will add a line in the table GROUPS with for NOM_GROUP the value “NEED HOLIDAYS”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
DECLARE @CPT_ID as int --variable that will get the CPT_ID DECLARE MyCursor CURSOR - my cursor -- For all USER CPT_IDs that have a STATE = FATIGUE FOR SELECT ID_USER from USERS WHERE STATE='FATIGUE' OPEN MyCursor -- I'm starting my cursor -- I fill it with my 1st CP_ID returned by the request FETCH MyCursor INTO @CPT_ID -- As long as I have not processed all the CPT_IDs of the request WHILE @@fetch_Status = 0 BEGIN print @CPT_ID + ' - ' INSERT GROUPS(CPT_ID,NAME_GROUP) VALUES(@CPT_ID, 'VACATION_NEED') -- I fill it with the following CP_ID returned by the request FETCH MyCursor INTO @CPT_ID END CLOSE myCursor -- I close my cursor DEALLOCATE myCursor -- I release the memory allocated to this cursor Print '----------------- Completed------------------------' |
