הבלוג של גרי רשף

01/09/2011

פרוצדורה להעתקת נתוני טבלה מהייצור לפיתוח

Filed under: Uncategorized — גרי רשף @ 23:04

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

להלן הפרוצדורה המוערת, ואחריה ההסברים:

Use Master;
Go

Create Proc SP_CopyFromHistory  @Tbl Sysname, --הטבלה שיש להעתיק
                                @Sharat Sysname, --השרת ממנו יש להעתיק
                                @Taarih DateTime, --נתונים לאיזה תאריך יש להעתיק
                                @TaarihCol Sysname --שם עמודת התאריך בטבלה
                                As
Declare @Cols Varchar(Max), --משתנה המקבל את שמות העמודות בטבלה לבניית הפקודה הדינאמית
        @SQL NVarchar(Max), --המשתנה המקבל את הפקודות הדינאמיות
        @Prm NVarchar(Max); --הפרמטר של הפרמטר המאפשר לקבל את הפלט מהקוד הדינאמי לאחר שהופעל
Select  @Prm='@Cols1 Varchar(Max) Output';
Set     @SQL='Select @Cols1=IsNull(@Cols1+'','','''')+''[''+C1.name+'']'''+Char(13)+ --הפקודה לקבלת שמות העמודות
        'From sys.tables T1'+Char(13)+
        'Inner Join sys.columns C1'+Char(13)+
        ' On T1.Object_id=C1.object_id'+Char(13)+
        'Inner Join ['+@Sharat+'].['+DB_Name()+'].sys.tables T2'+Char(13)+
        ' On T1.name=T2.name'+Char(13)+
        'Inner Join ['+@Sharat+'].['+DB_Name()+'].sys.columns C2'+Char(13)+
        ' On T2.Object_id=C2.object_id'+Char(13)+
        ' And C1.name=C2.name'+Char(13)+
        'Where T1.name='''+@Tbl+''';';
Execute SP_ExecuteSQL @SQL, --הפעלת הפקודה לקבלת שמות העמודות
                      @Prm,
                      @Cols1=@Cols Output;--העברת שמות העמודות מתוך הפעלת הפקודה הדינאמית לפרוצדורה
Select  @SQL='Delete From ['+@Tbl+'] Where ['+@TaarihCol+']='''+Convert(Varchar(Max),@Taarih,112)+''';'; --פקודת מחיקת הנתונים הישנים הדינאמית
Print   @SQL; --טוב לראות את פקודת המחיקה לפני שמפעילים אותה
Exec(@SQL); --הפעלת פקודת המחיקה
Select  @SQL='Insert'+Char(13)+ --פקודת ההעתקה הדינאמית
        'Into '+@Tbl+'('+@Cols+')'+Char(13)+
        'Select '+@Cols+Char(13)+
        'From ['+@Sharat+'].['+DB_Name()+'].dbo.['+@Tbl+']'+Char(13)+
        'Where ['+@TaarihCol+']='''+Convert(Varchar(Max),@Taarih,112)+''';';
Print   @SQL; --טוב לראות את פקודת ההעתקה לפני שמפעילים אותה
Exec(@SQL); --הפעלת פקודת ההעתקה
GO

Exec SP_MS_MarkSystemObject 'SP_CopyFromHistory';
Go

הבעייה העיקרית כאן היא כתיבת קוד דינאמי ברמה כפולה. למה הכוונה? לכאורה צריך לכתוב פקודה דינאמית בסגנון של Insert Into .. Select .. From ..Where .. ובה לציין את שם הטבלה ושם ה-Linked Server במקומות המתאימים וכו'; אלא שכדי לקבל את שמות העמודות יש ליצור פקודה דינאמית נפרדת (בגלל הצורך לפנות לשרת ששמו מועבר כפרמטר), פקודה שאינה מבצעת עדכון או שליפה של נתונים מטבלה אלא משרשרת את שמות העמודות המשותפות.

פקודה דינאמית זו יוצרת Join בין טבלאות המערכת בשתי הסביבות כדי לקבל רק עמודות הקיימות בשתיהן ולשרשר אותן,

היא מופעלת על ידי SP_ExecuteSQL שמאפשרת להעביר ערך משתנה שבתוך הקוד הדינאמי (Cols1@) למשתנה בפרוצדורה שמחוץ לו (Cols@), ועם זה ליצור את פקודת ה-Insert Into הדינאמית.

שם הדטבייס אליו פונים הוא כשם זה בו עובדים כרגע- ()DB_Name (מופיע בקוד ה-Insert הדינאמי).

את שמות האובייקטים תמיד טוב ["לעטוף" בסוגריים מרובעים] למקרה שיש בהם רווחים או נקודות וכו'.

בין לבין יש ליצור ולהריץ פקודה דינאמית למחיקת הנתונים הישנים על ידי Delete .. From,

ולהפעיל את שתי הפקודות הדינאמיות (מחיקת הנתונים הישנים וההעברה מהייצור) בעזרת Exec.

כדי שהפרוצדורה תהפוך לפרוצדורת מערכת שזמינה בכל הדטבייסים יש ליצור אותה בדטבייס master,

צריך שהשם שלה יתחיל ב-SP (במקרה זה SP_CopyFromHistory),

ולבסוף לסמן אותה כפרוצדורה מערכת בעזרת SP_MS_MarkSystemObject.

גם מי שלא הבין את הכל- הקוד מוכן לשימוש וצריך רק להריץ אותו עם ההרשאות המתאימות,

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

ניכנס לשרת הייצור וניצור טבלה בדטבייס Tempdb שכוללת עמודת תאריך בשם MyCol ובה התאריך 01/09/2011:

Use tempdb;
Go

Select  Cast('20110901' As DateTime) MyCol,
        *
Into    Tmp
From    sys.objects;
Go

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

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

Exec SP_CopyFromHistory 'Tmp','MyLinkedServer','20110901',MyCol;
Go

image

מודעות פרסומת

2 תגובות »

  1. […] ולא אחר. זה הופך אותן לנוחות לשימוש, למשל- בעבר הצגתי פרוצדורה להעתקת נתוני טבלה מהייצור לפיתוח. בגרסה בה אני משתמש במקום העבודה ניתן להפעיל את […]

    פינגבאק של שימוש בערכי ברירות לפרמטרים בפרוצדורות ופונקציות - גרי רשף — 01/12/2011 @ 21:42

  2. […] ולא אחר. זה הופך אותן לנוחות לשימוש, למשל- בעבר הצגתי פרוצדורה להעתקת נתוני טבלה מהייצור לפיתוח. בגרסה בה אני משתמש במקום העבודה ניתן להפעיל את […]

    פינגבאק של שימוש בערכי ברירות לפרמטרים בפרוצדורות ופונקציות « הבלוג של גרי רשף — 01/12/2011 @ 21:38


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

%d בלוגרים אהבו את זה: