|
Convert a SQL Query to a Stored Procedure
I am comfortable creating queries in SQL, but have not been successful at writing efficient stored procedures. I am attempting to create a stored procedure that grabs two identical tables and populate a single table (one has active data and the other history). The following query can grabs the data, but is extremely slow.
Also, I have not been able to use the '*' in place of the individual field names under 'SELECT' since there is a memo field in the tables (identical in both tables, but it gives me a distinct record error when I try to include this field).
-----------------------------------
SELECT demand_id, actgr_id, actual_dt, actual_tm, auto_rollover, bpart_descr, bpart_gcl_id, bpart_id, cost, created_by, creation_datetime, del_flag, demand_source, demand_stat_uni_id, demand_type_id, estimated_dt, estimated_tm, euro_total_cost, fullfiled_by, gquotel_id, holiday_cost, is_billable, is_external, is_fullfiled, is_long_text, is_std_populated, last_action_dt, last_change_by, last_change_date, last_change_db, last_change_seq, line_no, node_total_cost, order_id, over_cost, over_cost_2, p_key, person_id, priority, proj_task_id, qty, reg_cost, request_id, sa_person_id, stub_flag, total_cost, vendor_id, weekend_cost, z_udf_float_1, z_udf_string_1, call_type_id, descr, is_split, orginal_qty, parent_demand_id, payment_type_id, time_expense_sheet_id, zone_id
FROM wolfgang.dbo.demand AS A
WHERE NOT EXISTS
(SELECT * FROM "wolfgang"."dbo"."demand_done"
WHERE A.demand_id = demand_id)
UNION
SELECT demand_id, actgr_id, actual_dt, actual_tm, auto_rollover, bpart_descr, bpart_gcl_id, bpart_id, cost, created_by, creation_datetime, del_flag, demand_source, demand_stat_uni_id, demand_type_id, estimated_dt, estimated_tm, euro_total_cost, fullfiled_by, gquotel_id, holiday_cost, is_billable, is_external, is_fullfiled, is_long_text, is_std_populated, last_action_dt, last_change_by, last_change_date, last_change_db, last_change_seq, line_no, node_total_cost, order_id, over_cost, over_cost_2, p_key, person_id, priority, proj_task_id, qty, reg_cost, request_id, sa_person_id, stub_flag, total_cost, vendor_id, weekend_cost, z_udf_float_1, z_udf_string_1, call_type_id, descr, is_split, orginal_qty, parent_demand_id, payment_type_id, time_expense_sheet_id, zone_id
FROM wolfgang.dbo.demand_done AS B
WHERE NOT EXISTS
(SELECT * FROM "wolfgang"."dbo"."demand"
WHERE B.demand_id = demand_id)
|
ASP Free Forums Sponsor:
June 9th, 2004, 05:55 PM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
