New T-SQL Command – Merge

By | 15 ביוני 2010

הורדה של דוגמאות הקוד אשר במאמר

זהו מאמר שני בסדרת מאמרים אשר יסבירו את רכיבי הפקודות אשר נועדו על מנת לנהל שינויים על מימד (SCD – Slowly Changing Dimension) . המאמר הראשון היה על שימוש ב- Output בפקודות Delete, Update, Insert, Merge. מאמר זה יהיה על פקודת T-SQL חדשה (החל מ- SQL Server 2008) – Merge.

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

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

הסקריפט הבא בונה שתי טבלאות המדמות מימד לקוחות – dmnLakoah, וטבלה עם נתונים חדשים של לקוחות – Ods_Lakoah הכוללים גם רשומות חדשות וגם עדכוני מאפיינים לרשומות קיימות.

בטבלת המימד, אנא התעלמו מהשדות: PailLoPail, TaarichPtiha, TaarichSgira. נעשה בשדות אלו שימוש מאוחר יותר.


— בניית טבלת מימד הלקוחות

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.dmnLakoah') AND type in (N'U'))

DROP TABLE dbo.dmnLakoah

CREATE TABLE dbo.dmnLakoah(

Gkakoah int IDENTITY(1,1) NOT NULL,

MisZehut int NOT NULL,

ShemLakoah varchar(20) NOT NULL,

YeshuvMegurim varchar(20) NOT NULL,

RamatSahar char(1) NOT NULL,

DerugAshray char(2) NOT NULL,

PailLoPail bit NOT NULL,

TaarichPtiha date NOT NULL,

TaarichSgira date NOT NULL

) ON PRIMARY

— אכלוס הטבלה במספר רשומות

INSERT INTO tempdb.dbo.dmnLakoah

(MisZehut

,ShemLakoah

,YeshuvMegurim

,RamatSahar

,DerugAshray

,PailLoPail

,TaarichPtiha

,TaarichSgira)


VALUES

(279754

,'משה כהן'

,'קרני חיטים'

,'א'

,'AA'

,1

,'2010-5-30'

,'9999-12-31')

,

(234875

,'יובל לוי'

,'תל אביב'

,'ב'

,'B'

,1

,'2010-5-30'

,'9999-12-31')

,

(5498124

,'יונת שמעון'

,'חיפה'

,'ג'

,'A'

,1

,'2010-5-30'

,'9999-12-31')

,

(948102

,'דלית יוסף'

,'עפולה'

,'א'

,'AA'

,1

,'2010-5-30'

,'9999-12-31')

————————————————-

— בניה של טבלה עם נתוני לקוחות עדכניים

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Ods_Lakoah') AND type in (N'U'))

DROP TABLE dbo.Ods_Lakoah

CREATE TABLE dbo.Ods_Lakoah(

MisZehut int NOT NULL,

ShemLakoah varchar(20) NOT NULL,

YeshuvMegurim varchar(20) NOT NULL,

RamatSahar char(1) NOT NULL,

DerugAshray char(2) NOT NULL,

)


ON PRIMARY

———————————–

— אכלוס נתוני לקוח עדכניים

— אכלוס הטבלה במספר רשומות

INSERT INTO tempdb.dbo.Ods_Lakoah

(MisZehut

,ShemLakoah

,YeshuvMegurim

,RamatSahar

,DerugAshray

)


VALUES

(279754

,'משה כהן'

,'קרני חיטים'

,'א'

,'AA')

,

(234875

,'יובל לוי'

,'תל אביב'

,'ב'

,'B')

,

(5498124

,'יונת שמעון'

,'חיפה'

,'ג'

,'A')

,

(948102

,'דלית יוסף'

,'עפולה'

,'ב'

,'B+'),

(1234

, 'יואל משה'

, 'תל אביב'

, 'ג'

,'C')

,

(934134,

'סלומון יעקב'

, 'הרצליה'

, 'ב'

, 'A')

;

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

Merge dbo.dmnLakoah as Matara


using

(select
— Create a data set which will be compare with the dimension (Matara) to

[MisZehut]         — identify the new records.


,[ShemLakoah]


,[YeshuvMegurim]


,[RamatSahar]


,[DerugAshray]

from dbo.Ods_Lakoah)
as Makor


on Matara.MisZehut = Makor.MisZehut — The buisness key that will be used to cpmpare the dimension


when
not
matched
then — With the new data set

insert
— רשימת שדות אשר אליהם ירשמו ערכים ברשומה החדשה

( [MisZehut]


,[ShemLakoah]


,[YeshuvMegurim]


,[RamatSahar]


,[DerugAshray]


,[PailLoPail]


,[TaarichPtiha]


,[TaarichSgira])

values

(makor.MisZehut


,makor.ShemLakoah


,makor.YeshuvMegurim


,makor.RamatSahar


,makor.DerugAshray


,1 – PailLoPail משמעות שדה זה תוסבר במאמר הבא


,
getdate()
– תאריך פתיחה. משמעות שדה זה תוסבר במאמר הבא


,
'9999-12-31'
– תאריך סגירה. משמעות שדה זה תוסבר במאמר הבא


)


;
— Merge MUST end with ; .

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

update Ods_Lakoah


set YeshuvMegurim='נצרת'


where MisZehut=948102;


update Ods_Lakoah


set YeshuvMegurim='רעננה'


where MisZehut=934134;

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

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

Merge dbo.dmnLakoah as Matara


using

(select
— Create a data set which will be compare with the dimension (Matara) to

[MisZehut]         — identify the new records.


,[ShemLakoah]


,[YeshuvMegurim]


,[RamatSahar]


,[DerugAshray]

from dbo.Ods_Lakoah)
as Makor


on Matara.MisZehut = Makor.MisZehut — The buisness key that will be used to cpmpare the dimension


when
not
matched
then — With the new data set

insert
— רשימת שדות אשר אליהם ירשמו ערכים ברשומה החדשה

( [MisZehut]


,[ShemLakoah]


,[YeshuvMegurim]


,[RamatSahar]


,[DerugAshray]


,[PailLoPail]


,[TaarichPtiha]


,[TaarichSgira])

values

(makor.MisZehut


,makor.ShemLakoah


,makor.YeshuvMegurim


,makor.RamatSahar


,makor.DerugAshray


,1 – PailLoPail משמעות שדה זה תוסבר במאמר הבא


,
getdate()
– תאריך פתיחה. משמעות שדה זה תוסבר במאמר הבא


,
'9999-12-31'
– תאריך סגירה. משמעות שדה זה תוסבר במאמר הבא


)

when
matched
and
– עדכון רשומות קיימות במידה ובנתוני המקור עודכנו עיר מגורים או שם לקוח

(makor.YeshuvMegurim<>matara.YeshuvMegurim)
— /* חשוב להגדיר תנאים אלו על השדות שמעדכנים על מנת למנוע עדכון גורף אשר יגרום לכתיבת לוג מרובה ופגיעה בביצועים

*/

(makor.ShemLakoah<>matara.ShemLakoah)

then
update
set

YeshuvMegurim = matara.YeshuvMegurim

, ShemLakoah = Matara.ShemLakoah


;
— Merge MUST end with ; .

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

לפני ביצוע הפקודה נשנה מעט את תוכן טבלת הנתונים על מנת שה- Merge תהיה אפקטיבית.

— שינוי רשומה קיימת והוספת רשומה חדשה לטבלת המקור


update Ods_Lakoah


set YeshuvMegurim='בני ברק'


where MisZehut=934134;


INSERT
INTO [tempdb].[dbo].Ods_Lakoah


([MisZehut]


,[ShemLakoah]


,[YeshuvMegurim]


,[RamatSahar]


,[DerugAshray]


)


VALUES


(8265199


,'יעל גלבוע'


,'קבוצת שילר'


,'á'


,'C');

——————————————————————–

— Merge with Output


Merge dbo.dmnLakoah as Matara


using

(select

[MisZehut]        


,[ShemLakoah]


,[YeshuvMegurim]


,[RamatSahar]


,[DerugAshray]

from dbo.Ods_Lakoah)
as Makor


on Matara.MisZehut = Makor.MisZehut


when
not
matched
then

insert
( [MisZehut]


,[ShemLakoah]


,[YeshuvMegurim]


,[RamatSahar]


,[DerugAshray]


,[PailLoPail]


,[TaarichPtiha]


,[TaarichSgira])

values

(makor.MisZehut


,makor.ShemLakoah


,makor.YeshuvMegurim


,makor.RamatSahar


,makor.DerugAshray


,1


,
getdate()


,
'9999-12-31'


)

when
matched
and

(makor.YeshuvMegurim<>matara.YeshuvMegurim)
or
(makor.ShemLakoah<>matara.ShemLakoah)

then
update
set

YeshuvMegurim = matara.YeshuvMegurim

, ShemLakoah = Matara.ShemLakoah

OUTPUT
$action,makor.*
;

הורדה של דוגמאות הקוד אשר במאמר


Share

2 thoughts on “New T-SQL Command – Merge

  1. Pingback: ניהול שינויים היסטוריים במימד. Slowly Changing dimension with SQL Server SSIS | BI and More

  2. Pingback: ניהול שינויים במימד באמצעות T-SQL | BI and More

כתיבת תגובה

האימייל לא יוצג באתר.