September 6, 2006 at 7:27 am
I am rewriting an insert and update routine in SQL Server 2005. Once I run it, it completes without any errors but does not commit and parese or update transactions.
I am updating a 800K row table with 20K rows from import table with about 2k overlapping records.
Below the script (yes, it is very long, but that is also for visibility)
Any suggestions/ideas?
DROP TRIGGER IO_Trig_INS_import
Go
CREATE TRIGGER IO_Trig_INS_import ON import
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Claims. If there is no duplicate, do an insert.
IF NOT EXISTS (SELECT C.ts_repair_h_sourcing_cd, C.nation, C.product, C.model, C.serial_no, C.branch_cd, C.ship_to, C.claim_no, C.rcpt_no, C.svc_type, C.technician, C.purc_dt, C.receipt_dt, C.repair_stdt, C.repair_enddt, C.closing_dt, C.pulling_dt, C.cond_cd, C.symptom, C.section_cd, C.cause_cd, C.repair_cd, C.esn_no, C.chg_esn_no, C.sw_in_ver, C.sw_out_ver, C.operator_cd, C.operator_name, C.repair_time, C.ap_labor_amt_loc, C.ap_other_amt_loc, C.ap_hand_amt_loc, C.ap_visit_fee_loc, C.ap_tax_amt_loc, C.ap_part_amt_loc, C.ap_labor_amt_txn, C.ap_other_amt_txn, C.ap_hand_amt_txn, C.ap_visit_fee_txn, C.ap_tax_amt_txn, C.ap_part_amt_txn, C.part_no1, C.part_qty1, C.part_desc1, C.part_no2, C.part_qty2, C.part_desc2, C.part_no3, C.part_qty3, C.part_desc3, C.part_no4, C.part_qty4, C.part_desc4, C.part_no5, C.part_qty5, C.part_desc5, C.part_no6, C.part_qty6, C.part_desc6, C.part_no7, C.part_qty7, C.part_desc7, C.part_no8, C.part_qty8, C.part_desc8, C.part_no9, C.part_qty9, C.part_desc9, C.part_no10, C.part_qty10, C.part_desc10, C.part_no11, C.part_qty11, C.part_desc11, C.part_no12, C.part_qty12, C.part_desc12, C.part_no13, C.part_qty13, C.part_desc13, C.part_no14, C.part_qty14, C.part_desc14, C.part_no15, C.part_qty15, C.part_desc15, C.part_no16, C.part_qty16, C.part_desc16, C.part_no17, C.part_qty17, C.part_desc17, C.part_no18, C.part_qty18, C.part_desc18, C.part_no19, C.part_qty19, C.part_desc19, C.part_no20, C.part_qty20, C.part_desc20, C.circuit_no1, C.circuit_no2, C.circuit_no3, C.circuit_no4, C.circuit_no5, C.circuit_no6, C.circuit_no7, C.circuit_no8, C.circuit_no9, C.circuit_no10, C.circuit_no11, C.circuit_no12, C.circuit_no13, C.circuit_no14, C.circuit_no15, C.circuit_no16, C.circuit_no17, C.circuit_no18, C.circuit_no19, C.circuit_no20, C.warranty_days, C.currency, C.tech_remark, C.customer_remark, C.end_user_nm, C.tel_no, C.zip_no, C.warranty_flag, C.return_period, C.gcms_obu, C.auth_no, C.symptom_desc, C.cause_desc, C.repair_desc
FROM CBSIMPORT C
Inner Join inserted I on C.nation = I.nation AND C.rcpt_no = I.rcpt_no AND C.branch_cd = I.branch_cd)
INSERT tmp_CBS (ts_repair_h_sourcing_cd, nation, product, model, serial_no, branch_cd, ship_to, claim_no, rcpt_no, svc_type, technician, purc_dt, receipt_dt, repair_stdt, repair_enddt, closing_dt, pulling_dt, cond_cd, symptom, section_cd, cause_cd, repair_cd, esn_no, chg_esn_no, sw_in_ver, sw_out_ver, operator_cd, operator_name, repair_time, ap_labor_amt_loc, ap_other_amt_loc, ap_hand_amt_loc, ap_visit_fee_loc, ap_tax_amt_loc, ap_part_amt_loc, ap_labor_amt_txn, ap_other_amt_txn, ap_hand_amt_txn, ap_visit_fee_txn, ap_tax_amt_txn, ap_part_amt_txn, part_no1, part_qty1, part_desc1, part_no2, part_qty2, part_desc2, part_no3, part_qty3, part_desc3, part_no4, part_qty4, part_desc4, part_no5, part_qty5, part_desc5, part_no6, part_qty6, part_desc6, part_no7, part_qty7, part_desc7, part_no8, part_qty8, part_desc8, part_no9, part_qty9, part_desc9, part_no10, part_qty10, part_desc10, part_no11, part_qty11, part_desc11, part_no12, part_qty12, part_desc12, part_no13, part_qty13, part_desc13, part_no14, part_qty14, part_desc14, part_no15, part_qty15, part_desc15, part_no16, part_qty16, part_desc16, part_no17, part_qty17, part_desc17, part_no18, part_qty18, part_desc18, part_no19, part_qty19, part_desc19, part_no20, part_qty20, part_desc20, circuit_no1, circuit_no2, circuit_no3, circuit_no4, circuit_no5, circuit_no6, circuit_no7, circuit_no8, circuit_no9, circuit_no10, circuit_no11, circuit_no12, circuit_no13, circuit_no14, circuit_no15, circuit_no16, circuit_no17, circuit_no18, circuit_no19, circuit_no20, warranty_days, currency, tech_remark, customer_remark, end_user_nm, tel_no, zip_no, warranty_flag, return_period, gcms_obu, auth_no, symptom_desc, cause_desc, repair_desc)
SELECT CONVERT (nvarchar(2),ts_repair_h_sourcing_cd),
CONVERT (nvarchar(3), nation),
CONVERT (nvarchar(2), product),
CONVERT (nvarchar(25), model),
CONVERT (nvarchar(25), serial_no),
CONVERT (nvarchar(3), branch_cd),
CONVERT (nvarchar(15), ship_to),
CONVERT (nvarchar(25), claim_no),
CONVERT (nvarchar(25), rcpt_no),
CONVERT (nvarchar(2), svc_type),
CONVERT (nvarchar(35), technician),
purc_dt,
receipt_dt,
repair_stdt,
repair_enddt,
closing_dt,
pulling_dt,
CONVERT (nvarchar(3), cond_cd),
CONVERT (nvarchar(3), symptom),
CONVERT (nvarchar(5), section_cd),
CONVERT (nvarchar(3), cause_cd),
CONVERT (nvarchar(3), repair_cd),
CONVERT (nvarchar(50), esn_no),
CONVERT (nvarchar(50), chg_esn_no),
CONVERT (nvarchar(50), sw_in_ver),
CONVERT (nvarchar(50), sw_out_ver),
CONVERT (nvarchar(25), operator_cd),
CONVERT (nvarchar(50), operator_name),
CONVERT (Int, repair_time),
CONVERT (Money, ap_labor_amt_loc),
CONVERT (Money, ap_other_amt_loc),
CONVERT (Money, ap_hand_amt_loc),
CONVERT (Money, ap_visit_fee_loc),
CONVERT (Money, ap_tax_amt_loc),
CONVERT (Money, ap_part_amt_loc),
CONVERT (Money, ap_labor_amt_txn),
CONVERT (Money, ap_other_amt_txn),
CONVERT (Money, ap_hand_amt_txn),
CONVERT (Money, ap_visit_fee_txn),
CONVERT (Money, ap_tax_amt_txn),
CONVERT (Money, ap_part_amt_txn),
CONVERT (nvarchar (50), part_no1),
CONVERT (Int, part_qty1),
part_desc1,
CONVERT (nvarchar (50), part_no2),
CONVERT (Int, part_qty2),
part_desc2,
CONVERT (nvarchar (50), part_no3),
CONVERT (Int, part_qty3),
part_desc3,
CONVERT (nvarchar (50), part_no4),
CONVERT (Int, part_qty4),
part_desc4,
CONVERT (nvarchar (50), part_no5),
CONVERT (Int, part_qty5),
part_desc5,
CONVERT (nvarchar (50), part_no6),
CONVERT (Int, part_qty6),
part_desc6,
CONVERT (nvarchar (50), part_no7),
CONVERT (Int, part_qty7),
part_desc7,
CONVERT (nvarchar (50), part_no8),
CONVERT (Int, part_qty8),
part_desc8,
CONVERT (nvarchar (50), part_no9),
CONVERT (Int, part_qty9),
part_desc9,
CONVERT (nvarchar (50), part_no10),
CONVERT (Int, part_qty10),
part_desc10,
CONVERT (nvarchar (50), part_no11),
CONVERT (Int, part_qty11),
part_desc11,
CONVERT (nvarchar (50), part_no12),
CONVERT (Int, part_qty12),
part_desc12,
CONVERT (nvarchar (50), part_no13),
CONVERT (Int, part_qty13),
part_desc13,
CONVERT (nvarchar (50), part_no14),
CONVERT (Int, part_qty14),
part_desc14,
CONVERT (nvarchar (50), part_no15),
CONVERT (Int, part_qty15),
part_desc15,
CONVERT (nvarchar (50), part_no16),
CONVERT (Int, part_qty16),
part_desc16,
CONVERT (nvarchar (50), part_no17),
CONVERT (Int, part_qty17),
part_desc17,
CONVERT (nvarchar (50), part_no18),
CONVERT (Int, part_qty18),
part_desc18,
CONVERT (nvarchar (50), part_no19),
CONVERT (Int, part_qty19),
part_desc19,
CONVERT (nvarchar (50), part_no20),
CONVERT (Int, part_qty20),
part_desc20,
CONVERT (nvarchar(50), circuit_no1),
CONVERT (nvarchar(50), circuit_no2),
CONVERT (nvarchar(50), circuit_no3),
CONVERT (nvarchar(50), circuit_no4),
CONVERT (nvarchar(50), circuit_no5),
CONVERT (nvarchar(50), circuit_no6),
CONVERT (nvarchar(50), circuit_no7),
CONVERT (nvarchar(50), circuit_no8),
CONVERT (nvarchar(50), circuit_no9),
CONVERT (nvarchar(50), circuit_no10),
CONVERT (nvarchar(50), circuit_no11),
CONVERT (nvarchar(50), circuit_no12),
CONVERT (nvarchar(50), circuit_no13),
CONVERT (nvarchar(50), circuit_no14),
CONVERT (nvarchar(50), circuit_no15),
CONVERT (nvarchar(50), circuit_no16),
CONVERT (nvarchar(50), circuit_no17),
CONVERT (nvarchar(50), circuit_no18),
CONVERT (nvarchar(50), circuit_no19),
CONVERT (nvarchar(50), circuit_no20),
CONVERT (Int, warranty_days),
CONVERT (nvarchar(3), currency),
tech_remark,
customer_remark,
CONVERT (nvarchar(50), end_user_nm),
CONVERT (nvarchar(15), tel_no),
CONVERT (nvarchar(10), zip_no),
CONVERT (nvarchar(1), warranty_flag),
CONVERT (Int, return_period),
CONVERT (nvarchar(3), gcms_obu),
CONVERT (nvarchar(50),auth_no),
CONVERT (nvarchar(100), symptom_desc),
CONVERT (nvarchar(100), cause_desc),
CONVERT (nvarchar(100), repair_desc)
FROM inserted
ELSE
INSERT INTO DuplicateCBSimport
Select
CONVERT (nvarchar(2),ts_repair_h_sourcing_cd),
CONVERT (nvarchar(3), nation),
CONVERT (nvarchar(2), product),
CONVERT (nvarchar(25), model),
CONVERT (nvarchar(25), serial_no),
CONVERT (nvarchar(3), branch_cd),
CONVERT (nvarchar(15), ship_to),
CONVERT (nvarchar(25), claim_no),
CONVERT (nvarchar(25), rcpt_no),
CONVERT (nvarchar(2), svc_type),
CONVERT (nvarchar(35), technician),
purc_dt,
receipt_dt,
repair_stdt,
repair_enddt,
closing_dt,
pulling_dt,
SUSER_SNAME(),
GETDATE()
FROM inserted
-- Check on duplicates
If EXISTS (SELECT C.ts_repair_h_sourcing_cd, C.nation, C.product, C.model, C.serial_no, C.branch_cd, C.ship_to, C.claim_no, C.rcpt_no,
C.svc_type, C.technician, C.purc_dt, C.receipt_dt, C.repair_stdt, C.repair_enddt, C.closing_dt, C.pulling_dt, C.cond_cd, C.symptom, C.section_cd,
C.cause_cd, C.repair_cd, C.esn_no, C.chg_esn_no, C.sw_in_ver, C.sw_out_ver, C.operator_cd, C.operator_name, C.repair_time, C.ap_labor_amt_loc,
C.ap_other_amt_loc, C.ap_hand_amt_loc, C.ap_visit_fee_loc, C.ap_tax_amt_loc, C.ap_part_amt_loc, C.ap_labor_amt_txn, C.ap_other_amt_txn, C.ap_hand_amt_txn,
C.ap_visit_fee_txn, C.ap_tax_amt_txn, C.ap_part_amt_txn, C.part_no1, C.part_qty1, C.part_desc1, C.part_no2, C.part_qty2, C.part_desc2, C.part_no3,
C.part_qty3, C.part_desc3, C.part_no4, C.part_qty4, C.part_desc4, C.part_no5, C.part_qty5, C.part_desc5, C.part_no6, C.part_qty6, C.part_desc6, C.part_no7,
C.part_qty7, C.part_desc7, C.part_no8, C.part_qty8, C.part_desc8, C.part_no9, C.part_qty9, C.part_desc9, C.part_no10, C.part_qty10, C.part_desc10,
C.part_no11, C.part_qty11, C.part_desc11, C.part_no12, C.part_qty12, C.part_desc12, C.part_no13, C.part_qty13, C.part_desc13, C.part_no14, C.part_qty14,
C.part_desc14, C.part_no15, C.part_qty15, C.part_desc15, C.part_no16, C.part_qty16, C.part_desc16, C.part_no17, C.part_qty17, C.part_desc17,
C.part_no18, C.part_qty18, C.part_desc18, C.part_no19, C.part_qty19, C.part_desc19, C.part_no20, C.part_qty20, C.part_desc20, C.circuit_no1,
C.circuit_no2, C.circuit_no3, C.circuit_no4, C.circuit_no5, C.circuit_no6, C.circuit_no7, C.circuit_no8, C.circuit_no9, C.circuit_no10, C.circuit_no11, C.circuit_no12,
C.circuit_no13, C.circuit_no14, C.circuit_no15, C.circuit_no16, C.circuit_no17, C.circuit_no18, C.circuit_no19, C.circuit_no20, C.warranty_days, C.currency,
C.tech_remark, C.customer_remark, C.end_user_nm, C.tel_no, C.zip_no, C.warranty_flag, C.return_period, C.gcms_obu, C.auth_no, C.symptom_desc, C.cause_desc, C.repair_desc
FROM CBSIMPORT C
Inner Join inserted I on C.nation = I.nation AND C.rcpt_no = I.rcpt_no AND C.branch_cd = I.branch_cd)
UPDATE tmp_CBS
SET [ts_repair_h_sourcing_cd] = I.ts_repair_h_sourcing_cd,
[nation] = I.nation,
[product] = I.product,
[model] = I.model,
[serial_no] = I.serial_no,
[branch_cd] = I.branch_cd,
[ship_to] = I.ship_to,
[claim_no] = I.claim_no,
[rcpt_no] = I.rcpt_no,
[svc_type] = I.svc_type,
[technician] = I.technician,
[purc_dt] = I.purc_dt,
[receipt_dt] = I.receipt_dt,
[repair_stdt] = I.repair_stdt,
[repair_enddt] = I.repair_enddt,
[closing_dt] = I.closing_dt,
[pulling_dt] = I.pulling_dt,
[cond_cd] = I.cond_cd,
[symptom] = I.symptom,
[section_cd] = I.section_cd,
[cause_cd] = I.cause_cd,
[repair_cd] = I.repair_cd,
[esn_no] = I.esn_no,
[chg_esn_no] = I.chg_esn_no,
[sw_in_ver] = I.sw_in_ver,
[sw_out_ver] = I.sw_out_ver,
[operator_cd] = I.operator_cd,
[operator_name] = I.operator_name,
[repair_time] = I.repair_time,
[ap_labor_amt_loc] = I.ap_labor_amt_loc,
[ap_other_amt_loc] = I.ap_other_amt_loc,
[ap_hand_amt_loc] = I.ap_hand_amt_loc,
[ap_visit_fee_loc] = I.ap_visit_fee_loc,
[ap_tax_amt_loc] = I.ap_tax_amt_loc,
[ap_part_amt_loc] = I.ap_part_amt_loc,
[ap_labor_amt_txn] = I.ap_labor_amt_txn,
[ap_other_amt_txn] = I.ap_other_amt_txn,
[ap_hand_amt_txn] = I.ap_hand_amt_txn,
[ap_visit_fee_txn] = I.ap_visit_fee_txn,
[ap_tax_amt_txn] = I.ap_tax_amt_txn,
[ap_part_amt_txn] = I.ap_part_amt_txn,
[part_no1] = I.part_no1,
[part_qty1] = I.part_qty1,
[part_desc1] = I.part_desc1,
[part_no2] = I.part_no2,
[part_qty2] = I.part_qty2,
[part_desc2] = I.part_desc2,
[part_no3] = I.part_no3,
[part_qty3] = I.part_qty3,
[part_desc3] = I.part_desc3,
[part_no4] = I.part_no4,
[part_qty4] = I.part_qty4,
[part_desc4] = I.part_desc4,
[part_no5] = I.part_no5,
[part_qty5] = I.part_qty5,
[part_desc5] = I.part_desc5,
[part_no6] = I.part_no6,
[part_qty6] = I.part_qty6,
[part_desc6] = I.part_desc6,
[part_no7] = I.part_no7,
[part_qty7] = I.part_qty7,
[part_desc7] = I.part_desc7,
[part_no8] = I.part_no8,
[part_qty8] = I.part_qty8,
[part_desc8] = I.part_desc8,
[part_no9] = I.part_no9,
[part_qty9] = I.part_qty9,
[part_desc9] = I.part_desc9,
[part_no10] = I.part_no10,
[part_qty10] = I.part_qty10,
[part_desc10] = I.part_desc10,
[part_no11] = I.part_no11,
[part_qty11] = I.part_qty11,
[part_desc11] = I.part_desc11,
[part_no12] = I.part_no12,
[part_qty12] = I.part_qty12,
[part_desc12] = I.part_desc12,
[part_no13] = I.part_no13,
[part_qty13] = I.part_qty13,
[part_desc13] = I.part_desc13,
[part_no14] = I.part_no14,
[part_qty14] = I.part_qty14,
[part_desc14] = I.part_desc14,
[part_no15] = I.part_no15,
[part_qty15] = I.part_qty15,
[part_desc15] = I.part_desc15,
[part_no16] = I.part_no16,
[part_qty16] = I.part_qty16,
[part_desc16] = I.part_desc16,
[part_no17] = I.part_no17,
[part_qty17] = I.part_qty17,
[part_desc17] = I.part_desc17,
[part_no18] = I.part_no18,
[part_qty18] = I.part_qty18,
[part_desc18] = I.part_desc18,
[part_no19] = I.part_no19,
[part_qty19] = I.part_qty19,
[part_desc19] = I.part_desc19,
[part_no20] = I.part_no20,
[part_qty20] = I.part_qty20,
[part_desc20] = I.part_desc20,
[circuit_no1] = I.circuit_no1,
[circuit_no2] = I.circuit_no2,
[circuit_no3] = I.circuit_no3,
[circuit_no4] = I.circuit_no4,
[circuit_no5] = I.circuit_no5,
[circuit_no6] = I.circuit_no6,
[circuit_no7] = I.circuit_no7,
[circuit_no8] = I.circuit_no8,
[circuit_no9] = I.circuit_no9,
[circuit_no10] = I.circuit_no10,
[circuit_no11] = I.circuit_no11,
[circuit_no12] = I.circuit_no12,
[circuit_no13] = I.circuit_no13,
[circuit_no14] = I.circuit_no14,
[circuit_no15] = I.circuit_no15,
[circuit_no16] = I.circuit_no16,
[circuit_no17] = I.circuit_no17,
[circuit_no18] = I.circuit_no18,
[circuit_no19] = I.circuit_no19,
[circuit_no20] = I.circuit_no20,
[warranty_days] = I.warranty_days,
[currency] = I.currency,
[tech_remark] = I.tech_remark,
[customer_remark] = I.customer_remark,
[end_user_nm] = I.end_user_nm,
[tel_no] = I.tel_no,
[zip_no] = I.zip_no,
[warranty_flag] = I.warranty_flag,
[return_period] = I.return_period,
[gcms_obu] = I.gcms_obu,
[auth_no] = I.auth_no,
[symptom_desc] = I.symptom_desc,
[cause_desc] = I.cause_desc,
[repair_desc] = I.repair_desc
FROM CBSIMPORT C, inserted I
WHERE C.Nation = I.Nation AND C.rcpt_no = I.rcpt_no AND C.branch_cd = I.branch_cd
end
September 11, 2006 at 8:00 am
This was removed by the editor as SPAM
September 11, 2006 at 5:35 pm
Frans,
Could you give a littlel more information? The trigger addresses INSERT, but not update, so I'm not sure what behavior is expected.
Elliot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply