Sql Server התנהגות מפתח ראשי

[ארכיטקט]

שלום

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

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

כעת גם SQL server מוכן לשתף פעולה בדבר כזה, אולם כל זה בשאילתות הוספה שאני עושה דרך odbc לטבלה של ממש, אני פשוט מבטל את ההודעה אודות כך שאי אפשר להוסיף את כל הרשומות וכולי, והמשתמש לא מרגיש כלום ובא לציון גואל.

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

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

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

אם יש למישהו רעיון, זה יעזור מאוד.

תודה.

פורסם במקור בפורום CODE613 ב09/02/2014 00:56 (+02:00)

[clickone]

אתה מכניס הכל במכה, או עם לופ?
הייתי הולך על משהו בסיגנון של On error resume next בVB. (אני לא בטוח שזה קיים בSQL במתכונת הזו)
אבל מה שבטוח קיים זה בסגנון TRY של דוטנט.
דוגמא לTRY עם יצירת שגיאה בחילוק ב0:

BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    -- Execute error retrieval routine.
    
END CATCH;

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

בנוסף:
הטבלה שאליה אתה מוסיף היא זמנית או קבועה?
אם היא זמנית אז לא נורא שתעשה בסוף SELECT DISTINCT ואז תוסיף לקבועה או תעשה מה שבא לך עם הנתונים. אם היא קבועה, אז באמת זה לא כ"כ בא בחשבון.

פורסם במקור בפורום CODE613 ב09/02/2014 01:30 (+02:00)

[ארכיטקט]

@ClickOne

אתה מכניס הכל במכה, או עם לופ?

הכל במכה
@ClickOne

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

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

@ClickOne

בנוסף:
הטבלה שאליה אתה מוסיף היא זמנית או קבועה?

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

@ClickOne

אם היא זמנית אז לא נורא שתעשה בסוף SELECT DISTINCT ואז תוסיף לקבועה או תעשה מה שבא לך עם הנתונים. אם היא קבועה, אז באמת זה לא כ"כ בא בחשבון.

לא זה הסיפור, אני עושה כאן קומבינה שקשה להשיג אותה ב SELECT DISTINCT (אם כי יש סיכוי ראיתי איזה מדריך מייגע) הרעיון הוא כזה, אני מכניס לפי סדר 4 שאילתות, שמבחינתי עדיף שאם יש ערכים בראשונה, הם ייכנסו ראשונים, ואם לא, אז מהשניה, ואם לא, אז מהשלישית וכולי. כעת כשאני עושה SELECT DISTINCT אני צריך להשתמש בפונקציה כלשהי כדי להחזיר את הערכים שאני חפץ ביקרם, על ידי פונקציות צבירה של group by (מכיון שהערכים הייחודיים נמצאים רק ב 2 עמודות, ואילו בעמודות האחרון יש ערכים שונים, שאני מעוניין בהם לפי סדר עדיפויות מסויים שאין אותו בפונקציית צבירה של group by). ואז זה לא מחזיר לי שום מידע אמין. זאת בעיקר הבעיה שלי. ומאז ומעולם הסתמכתי על התענוג הזה שכאשר עושים אינדקס ייחודי, הוא פשוט מונע כניסה ללא מורשים, וככה אני יכול לעשות סלקציה של האורחים לפי סדר עדיפויות משלי.

פורסם במקור בפורום CODE613 ב09/02/2014 02:02 (+02:00)

[clickone]

@ארכיטקט

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

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

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

פורסם במקור בפורום CODE613 ב09/02/2014 02:12 (+02:00)

[רחמים]

@ClickOne

הייתי מנסה לעבור על ההוספה בלופ + TRY, זה נשמע הכי פחות מלוכלך והכי קצר...

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

@ארכיטקט

אני עושה כאן קומבינה שקשה להשיג אותה ב SELECT DISTINCT (אם כי יש סיכוי ראיתי איזה מדריך מייגע) הרעיון הוא כזה, אני מכניס לפי סדר 4 שאילתות, שמבחינתי עדיף שאם יש ערכים בראשונה, הם ייכנסו ראשונים, ואם לא, אז מהשניה, ואם לא, אז מהשלישית וכולי. כעת כשאני עושה SELECT DISTINCT אני צריך להשתמש בפונקציה כלשהי כדי להחזיר את הערכים שאני חפץ ביקרם, על ידי פונקציות צבירה של group by (מכיון שהערכים הייחודיים נמצאים רק ב 2 עמודות, ואילו בעמודות האחרון יש ערכים שונים, שאני מעוניין בהם לפי סדר עדיפויות מסויים שאין אותו בפונקציית צבירה של group by). ואז זה לא מחזיר לי שום מידע אמין. זאת בעיקר הבעיה שלי. ומאז ומעולם הסתמכתי על התענוג הזה שכאשר עושים אינדקס ייחודי, הוא פשוט מונע כניסה ללא מורשים, וככה אני יכול לעשות סלקציה של האורחים לפי סדר עדיפויות משלי.

אם הבנתי את הבעיה נכון אז הפתרון יכול להיות כזה:
הכנס את כל 4 השאילתות לפי הסדר הרצוי לטבלה הזמנית
עשה שאילתא שתקח רק את 2 העמודות שיש בהם חשש כפילות עם DISTINCT , כך תסנן כפילויות
אחר כך תעשה שאילתא שתקח את שתי העמודות האחרות
אחר כך תעשה שאילתא שתחזיר את שני השאילתות הקודמות עם INNER JOIN וכך תקבל את כל ה 4 עמודות ללא כפיליות.
ואז את השאילתא האחרונה עם כל ה 4 עמודות תשים בטבלה הזמנית הקודמת או תעשה אחרת ומכאן הכל ממשיך כרגיל.

דרך אחרת יכולה להיות כך:
לאחר שכבר יש לך בטבלה הזמנית את 4 השאילתות לפי הסדר שאתה מעדיף, תוסיף לטבלה עמודה עם מספור אוטומטי רציף.
כעת תעשה GROUP BY כדי למנוע כפליות בעמודות הרציות
כעת בשביל להחזיר את הסדר הרצוי תעשה מיון בעמודה עם המספור האוטומטי שהוספת בהתחלה.

פורסם במקור בפורום CODE613 ב09/02/2014 07:48 (+02:00)

[ארכיטקט]

@ארכיטקט

אתה יכול לבדוק את זה? (פשוט תנסה להוסיף חלק כפול וחלק לא ותראה אם הוא הכניס את הכפול).

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

@רחמים

אחר כך תעשה שאילתא שתקח את שתי העמודות האחרות

ומי יגיד לי אחר כך לאיזה רשומות הן שייכות בעמודות הבלתי כפולות???
@רחמים

אחר כך תעשה שאילתא שתחזיר את שני השאילתות הקודמות עם INNER JOIN וכך תקבל את כל ה 4 עמודות ללא כפיליות.

INNER JOIN מאיפה לאיפה?? הרי בכל שאילתה יש רק 2 עמודות שונות ואין עמודה אחת שווה ביניהם.
@רחמים

דרך אחרת יכולה להיות כך:

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

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

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

כעת תעשה GROUP BY כדי למנוע כפליות בעמודות הרציות
כעת בשביל להחזיר את הסדר הרצוי תעשה מיון בעמודה עם המספור האוטומטי שהוספת בהתחלה.

לא מיון אלא פונקציית min.
כאן אתה צריך את הפתרון שאמרת למעלה, לעשות 2 שאילתות, ב SELECT DISTINCT לעשות GROUP BY על השדות הייחודיים, ולהפעיל פונקציית min על המזהה האוטומטי, על מנת להחזיר את הערכים הנמוכים ביותר בקבוצה, כלומר אלו שנכנסו ראשונים. לאחר מכן שאילתה נוספת שתעשה INNER JOIN עם הטבלה הזמנית וטבלת ה DISTINCT על עמודת ה min של ה ID ואז לקבל את הערכים הרצויים. זה כמובן עוד הרבה קוד מיותר אבל אם אין ברירה וזה מה שמייקרוסופט החליטו בשבילנו, לא תהיה ברירה.

פורסם במקור בפורום CODE613 ב09/02/2014 10:30 (+02:00)

[ארכיטקט]

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

פורסם במקור בפורום CODE613 ב09/02/2014 11:14 (+02:00)

[clickone]

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

עריכה:
ארכיטקט: אתה יכול בבקשה להעלות את קטע הקוד של ההוספה שנכשל בתוך הפרוצדורה?

פורסם במקור בפורום CODE613 ב09/02/2014 11:25 (+02:00)

[ארכיטקט]

@ClickOne

ארכיטקט: אתה יכול בבקשה להעלות את קטע הקוד של ההוספה שנכשל בתוך הפרוצדורה?

declare @table table ( Parent nvarchar(50), sub nvarchar(50)  UNIQUE  (parent))
insert into @table 
values ('one','one sub'),
('tow','tow sub')
BEGIN TRY
insert into @table 
values ('Three','Three sub'),
('tow','2 sub')
END TRY
BEGIN CATCH

END CATCH;
select * from @table

פורסם במקור בפורום CODE613 ב09/02/2014 11:30 (+02:00)

[dovid]

בדוק גם להשתמש עם UNION לפני ההכנסה, עם הטבלה הקיימת כבר.

פורסם במקור בפורום CODE613 ב09/02/2014 12:15 (+02:00)

[dovid]

לא הבנתי, ארכיטקט אמרת הכל במכה והבאת קוד של שורה בודדת.

מידי דברי גיגלתי ומצאתי http://stackoverflow.com/questions/1139050/how-to-ignore-duplicate-key-error-in-t-sql-sql-server
יש שם כמה פתרונות:
א. קביעת ערך במשתנה מערכת בשם XACT_ABORT
ב. כנ"ל עם IGNORE_DUP_KEY
ג. תנאי בהכנסה של אם קיים.
ד. יעויין שם...

פורסם במקור בפורום CODE613 ב09/02/2014 12:19 (+02:00)

[ארכיטקט]

@דוד ל.ט.

ג. תנאי בהכנסה של אם קיים.

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

אז אצל השכנים ב mysql החינמי כבר מזמן יש פתרון:

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

פורסם במקור בפורום CODE613 ב09/02/2014 16:10 (+02:00)

[dovid]

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

בשולי הדברים: המאפיין XACT_ABORT קובע האם כל הפעולות בטרנזקציה יחשבו ככישלון במקרה שגיאה במשפט אחד. אתה צודק שזה לא עוזר ל"דלג" על שורות במשפט אחד.

ואכן לא לגמרי הבנתי את כל התוכנית. לא הבנתי למה א"א להשתמש בתנאי עם קיים אפי' בגוף השאילתה. ולמה Group by מסובך, ומה לגבי UNION וMERGE.

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

פורסם במקור בפורום CODE613 ב09/02/2014 16:45 (+02:00)

[ארכיטקט]

@דוד ל.ט.

חייבת להיות דרך

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

אני לא בסוגיא שלך (אני חוסך ככה התנצלות...), ואני לא אתחיל לנסות את כל הבלגן כדי למצוא תשובה

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

ואכן לא לגמרי הבנתי את כל התוכנית.

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

@דוד ל.ט.

אבל אל תצפה שאני אעבוד בגלל שמישהו הפריח שאלות

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

פורסם במקור בפורום CODE613 ב09/02/2014 19:56 (+02:00)

[ארכיטקט]

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

פורסם במקור בפורום CODE613 ב08/05/2014 21:02 (+03:00)