一、临时表妙用
-- 1、将老库中的mediaid和新库中的regionid对应上，然后插入到临时表中
SELECT * INTO
#TempMediaRegion FROM (SELECT ww.C_MEDIA,C_ID FROM
[NEWSYS].[LMSData].[dbo].Base_Region
right JOIN openquery(CRM, ‘select *
from T_LOCREGION‘) ww
ON C_CrmID=ww.C_REGION) hh
--
2、将媒体按媒体分组
SELECT * INTO #TempLocalMediaRegion FROM (SELECT
ww.C_MEDIA,
stuff((select ‘,‘+cast(C_ID as NVARCHAR(50)) FROM
#TempMediaRegion where C_MEDIA=ww.C_MEDIA for xml path(‘‘)),1,1,‘‘) b
FROM
#TempMediaRegion ww GROUP BY C_MEDIA) sf
--3、 更新媒体表（地区）
UPDATE
[NEWSYS].[LMSData].[dbo].Base_Media SET C_RegionID=#TempLocalMediaRegion.b FROM
[NEWSYS].[LMSData].[dbo].Base_Media
LEFT JOIN #TempLocalMediaRegion ON
C_CrmID=#TempLocalMediaRegion.C_MEDIA
二、连表更新
-- 更新
UPDATE M SET M.C_ParentId = MM.c_id FROM dbo.Base_Media M LEFT JOIN
dbo.Base_Media MM ON M.c_crmparentid = mm.C_CrmID
三、sqlserver 相当于 mysql contact的用法
SELECT * INTO #TempLocalMediaRegion FROM (SELECT ww.C_MEDIA,
stuff((select
‘,‘+cast(C_ID as NVARCHAR(50)) FROM #TempMediaRegion where C_MEDIA=ww.C_MEDIA
for xml path(‘‘)),1,1,‘‘) b
FROM #TempMediaRegion ww GROUP BY C_MEDIA)
sf