האופרטור Output

האופרטור Output כחלק מפקודת Update / Insert / Delete מאפשר לנו לקבל פלט של השורות שהשתנו או נכנסו או נמחקו בפקודה: זה טוב לבקרה, לתיעוד, ולשימושים אחרים.

ה-Output מוכר ושימושי בעיקר בטריגרים בהם מטפלים בשורות שהשתנו, אבל הנה - גם בפקודות DML שגרתיות יש לו שימוש, למשל -

ניצור טבלה, נבצע Insert, ואת השורות שהוספנו לטבלה נשלח בנוסף למסך:

Drop Table If Exists #T;

Create Table #T(Txt Varchar(10),ID Int Default 0);

Go

Insert

Into   #T(Txt)

Output Inserted.*

Values ('Avi'),('Bilha'),('Carmel'),('Dan'),('Eli'),('Frida'),('Gaby');

Go

נבצע פקודת Update תוך שימוש ב-Output לשמות שכוללים את האות l:

Update #T

Set           ID+=1

Output Inserted.*,Deleted.*

Where  Txt Like '%l%';

כפי שאפשר לראות - הסט Deleted כולל את הערכים לפני השינוי, והסט Inserted את הערכים המעודכנים.

נחזור על העדכון הנ"ל שוב, אבל הפעם נשלח את הערכים המעודכנים גם לטבלה אחרת וגם למסך (בהמשך נראה למה זה טוב):

Drop Table If Exists #T2;

Create Table #T2(Txt Varchar(10),ID Int Default 0);

Go

Update #T

Set           ID+=1

Output Inserted.*

Into   #T2

Output Inserted.*,Deleted.*

Where  Txt Like '%l%';

האם ניתן להכניס את הערכים ליותר מטבלה אחת? עם קצת אקרובטיקה כנראה שכן, לא בטוח שאני יודע למה זה טוב, אך העיקר שזה אפשרי:

Create Table #T3(Txt Varchar(10),ID Int Default 0);

Go

Insert

Into   #T3

Exec('Update  #T

Set           ID+=1

Output Inserted.*

Into   #T2

Output Inserted.*

Where  Txt Like ''%l%'';');

במקרה זה אין פלט כי הוא נשלח ל-#T3, אבל אפשר לבדוק ולראות שהשורות שהתעדכנו התווספו לשתי הטבלאות הנוספות.

האם ניתן להכניס את השורות שהשתנו ליותר משתי טבלאות? אני לא הצלחתי, אבל זו אינה ראייה לשום דבר..

ולבסוף פקודת Delete תוך הפניית השורות שנמחקו לטבלה אחרת:

Delete

From   #T

Output Deleted.*

Into   #T2

Where  Txt Like '%l%';

עם קצת משובת נעורים ורוח הרפתקנית ניתן לשלוח את השורות שנמחקו חזרה לתוך הטבלה, מה שמזכיר לי את אבא שלי שהיה מכין פסטה, וכשהיה מסנן אותה - הוא היה מנסה לתפוס בחזרה את מי הבישול עם הסיר בו הפסטה בושלה (אולי כדי לחסוך מים וגם להימנע מללכלך עוד כלים).

ביכולת זו, של הכנסת השורות שנמחקו בטבלה אחת לטבלה אחרת, אני משתמש כאשר אני צריך להעביר שורות מטבלה לטבלה; נניח מטבלה שוטפת לטבלת היסטוריה. ניתן לעשות זאת כמקובל על ידי כך שנשתמש באותו תנאי לביצוע Insert Into From ולביצוע Delete או שנמחק בכל פעם את מה שבטבלת ההיסטוריה מהטבלה השוטפת תוך שאנחנו עוטפים את הכל בטרנזקציה כדי לוודא שמה שהתווסף כאן נמחק שם ולהיפך, ולבדוק בשבע עיניים שלא פספסנו שום דבר; אבל אם משתמשים ב-Output כנ"ל - שום דבר כזה לא יכול להשתבש: או שהשורות נמחקו מהשוטפת ונכנסו להיסטוריה, או שאף שורה לא נמחקה ולא התווספה, וכל זה בפקודה אחת ללא טרנזקציה מפורשת.

כמובן שזה שימושי גם ובעיקר כשמבצעים את ההעברה ב-batches ולא את הכל בפעם אחת.

ולסיום פקודת Merge שנועדה לאפשר סנכרון בין שתי טבלאות, בה מעדכנים את טבלת היעד על פי טבלת המקור: לא רק עדכון של ערכים קיימים, אלא במקרה הצורך - מחיקה שלערכים מיותרים או או הוספה של חסרים.

ניצור שתי טבלאות עם נתונים שונים, במקרה זה המבנה שלהם זהה אך זה לא הכרחי:

Drop Table If Exists #Source,#Target;

Create Table #Source

              (ID Int Primary Key,

              Txt Varchar(10));

Insert

Into   #Source(ID,Txt)

Values (1,'a'),(2,'b'),(4,'d'),(5,'e');

Create Table #Target

              (ID Int Primary Key,

              Txt Varchar(10));

Insert

Into   #Target(ID,Txt)

Values (1,'aa'),(2,'bb'),(3,'cc'),(6,'FF');

Go

Select * From #Source;

Select * From #Target;

במקרה זה לא השתמשתי באופרטור Output להצגת השורות שנכנסו לטבלאות כדי לא ליצור בלבול: הוא רלוונטי כרגע לפקודת ה-Merge בהמשך ולא להכנסת השורות לטבלאות.

ניתן לראות שיש 2 שורות עם מפתח ID זהה בשתי הטבלאות אבל עם ערכי Txt שונים, 2 שורות שנמצאות במקור אך לא ביעד, ו-2 שורות שנמצאות ביעד אך לא במקור. בדוגמה שלהלן נטפל בכולם ונגרום לטבלת היעד להיות זהה למקור. יכולתי לעשות זאת על ידי Truncate לטבלת היעד ו-Insert Into From מטבלת המקור, אבל אני מנצל את ההזדמנות כדי להציג את שלל היכולות של ה-Merge למקרה הצורך:

MERGE  #Target As T

Using  #Source As S

              ON (T.ID=S.ID)

WHEN   MATCHED THEN 

              Update

              Set    T.Txt=S.Txt

WHEN   NOT MATCHED BY TARGET THEN

              Insert (ID,Txt) 

              Values(S.ID,S.Txt)

WHEN   NOT MATCHED BY SOURCE THEN

              Delete

Output $Action,Deleted.ID Del_ID,Deleted.Txt Del_Txt, Inserted.ID Ins_ID, Inserted.Txt Ins_Txt;

במקרה זה התווסף לפסוקית ה-Output גם הפרמטר Action$ שמציין איזו פעולה בוצעה בכל שורה, ולגבי כל שורה ניתן לראות מה הערך הקודם(אם היה) ומה הערך החדש (אם יש). ניתן לבדוק ולוודא ששתי הטבלאות זהות, אני לרוב עושה זאת כך:

Select * From #Source

Except

Select * From #Target;

Select * From #Target

Except

Select * From #Source;

פעם אחת אני "מחסיר" את השנייה מהראשונה, ופעם אחת להיפך; ואם בשני המקרים חוזרים סטים ריקים - משמע שהטבלאות זהות ואין באף אחת שורות שאין בשנייה.