insert_missing_activity_contacts.sql

来自「国外的一套开源CRM」· SQL 代码 · 共 38 行

-- This script inserts the activity owner as a participant on any activities for which the owner is not
-- already a participant.

----------
-- Fill in the missing owner IDs on activities.
----------

update activity
set owner_id = created_by
where owner_id is null;

----------
-- Write script to add the activity contacts.
----------

select concat(
	'insert into activity_contact (activity_id, contact_id, created_by, created_date, modified_by, modified_date) values (\'',
	a.activity_id,
	'\', \'',
	a.owner_id,
	'\', \'',
	a.created_by,
	'\', \'',
	a.created_date,
	'\', \'',
	a.modified_by,
	'\', \'',
	a.modified_date,
	'\');')
into outfile 'c:/temp/insert_missing_activities.sql'
from activity a
left join activity_contact ac on ac.activity_id = a.activity_id
where ac.activity_id is null
order by a.activity_id;

source c:/temp/insert_missing_activities.sql;