new.sql

SELECT 	DISTINCT 
	aus.author_set_id, 
	aus.author_set_name, 
	m.media_id, 
	rcp.roi_campaign_id
INTO #T_MEDIA_AUTHOR_SET
FROM 	media m
	INNER JOIN media_item mi ON (mi.media_id = m.media_id)
	INNER JOIN provider_media_item drmi ON (drmi.media_item_id = mi.media_item_id)
	INNER JOIN author_set aus ON (aus.author_set_id = drmi.author_set_id)
	INNER JOIN provider dr ON (dr.provider_id = drmi.provider_id)
	INNER JOIN roi_campaign_project rcp ON (rcp.project_id = dr.project_id and rcp.project_id = aus.project_id)

original.sql

SELECT 	DISTINCT 
	aus.author_set_id, 
	aus.author_set_name, 
	m.media_id, 
	rcp.roi_campaign_id
INTO #T_MEDIA_AUTHOR_SET
FROM 	media m
	INNER JOIN media_item mi ON (mi.media_id = m.media_id)
	INNER JOIN provider_media_item drmi ON (drmi.media_item_id = mi.media_item_id)
	INNER JOIN author_set aus ON (aus.author_set_id = drmi.author_set_id)
	INNER JOIN provider dr ON (dr.provider_id = drmi.provider_id)
	INNER JOIN roi_campaign_project rcp ON (rcp.project_id = dr.project_id)

INSERT INTO mip_dw.dbo.media_profile_author_set (
	author_set_id, 
	media_id, 
	roi_campaign_id, 
	author_set_name )
SELECT 	t.author_set_id, 
	t.media_id, 
	t.roi_campaign_id, 
	t.author_set_name
FROM 	#T_MEDIA_AUTHOR_SET t,
	mip_dw.dbo.media_profile m
WHERE 	t.roi_campaign_id = m.roi_campaign_id
AND 	t.media_id = m.media_id