sql问题

[emmahyt]

现在有两个表 我想比较table1 fundcode 和table2 accountcode 把表2中表1所没有的accountcode添加到表1fundcode列中 list_tag,addeddate, updateddate,corpsacct和原来表1中一样 但是新添加的guelphaccts数字要改成0 希望可以直接把表1这么更新了 请问这样怎么写呀 感激大家
table1:

SELECT
[list_tag]
,[fundcode]
,[added_date]
,[updated_date]
,[CorpsAcct]
,[GuelphAccts]
FROM [aci_invest].[corfi].[fund_list]

table2:

Select

i.issuer_name as ISSUER_NAME,
f.fundcode as ACCOUNT_CODE,
i.issue_description as ISSUE_DESCRIPTION,
i.coupon_div_currency as INCOME_CURRENCY,


p.local_mv_accint as MARKET_VALUE_INCOME,

p.effective_date as VALUE_DATE,

ice.pf_class_level6 as DETAIL_CLASS_LEVEL_8_EN,

i.issuer_name_en as ISSUER_NAME_EN,
i.parent_issuer_name_en as PARENT_ISSUER_NAME_EN







from
main.vw_position p
join main.vw_fund f on p.effective_date=f.effective_date and p.fundcode=f.fundcode
join main.vw_instrument i on p.effective_date=i.effective_date and p.instrument_id=i.instrument_id
join main.vw_instrument_analytic ia on p.effective_date=ia.effective_date and p.instrument_id=ia.instrument_id
join main.vw_instrument_class_en ice on p.effective_date=ice.effective_date and p.instrument_id=ice.instrument_id
left join main.vw_instrument_rating ir on p.effective_date=ir.effective_date and p.instrument_id=ir.instrument_id

where
p.effective_date = '2018-01-18'

and (ice.pf_class_level1 = 'Cash' or ice.pf_class_level1 = 'Fixed Income')
and (p.lt_flag = 'S' or p.lt_flag = 'SL')
and (ice.pf_class_level2 = 'Bonds' or ice.pf_class_level2 = 'Money Market' or ice.pf_class_level2 = 'CASH' OR ice.pf_class_level2 = 'Mortgages' or ice.pf_class_level2 = 'OTHER')
and ice.pf_class_level5 = 'Corporate'
and (p.fundcode like 'A%' or p.fundcode like 'F%' or p.fundcode like 'X%')

[emmahyt]

我尝试这么写 但是报错：Msg 156, Level 15, State 1, Line 46 Incorrect syntax near the keyword 'into'. WITH cte AS ( SELECT i.issuer_name AS ISSUER_NAME , f.fundcode AS ACCOUNT_CODE , i.issue_description AS ISSUE_DESCRIPTION , i.coupon_div_currency AS INCOME_CURRENCY , p.local_mv_accint AS MARKET_VALUE_INCOME , p.effective_date AS VALUE_DATE , ice.pf_class_level6 AS DETAIL_CLASS_LEVEL_8_EN , i.issuer_name_en AS ISSUER_NAME_EN , i.parent_issuer_name_en AS PARENT_ISSUER_NAME_EN FROM main.vw_position p JOIN main.vw_fund f ON p.effective_date = f.effective_date AND p.fundcode = f.fundcode JOIN main.vw_instrument i ON p.effective_date = i.effective_date AND p.instrument_id = i.instrument_id JOIN main.vw_instrument_analytic ia ON p.effective_date = ia.effective_date AND p.instrument_id = ia.instrument_id JOIN main.vw_instrument_class_en ice ON p.effective_date = ice.effective_date AND p.instrument_id = ice.instrument_id LEFT JOIN main.vw_instrument_rating ir ON p.effective_date = ir.effective_date AND p.instrument_id = ir.instrument_id WHERE p.effective_date = '2018-01-18' AND ( ice.pf_class_level1 = 'Cash' OR ice.pf_class_level1 = 'Fixed Income' ) AND ( p.lt_flag = 'S' OR p.lt_flag = 'SL' ) AND ( ice.pf_class_level2 = 'Bonds' OR ice.pf_class_level2 = 'Money Market' OR ice.pf_class_level2 = 'CASH' OR ice.pf_class_level2 = 'Mortgages' OR ice.pf_class_level2 = 'OTHER' ) AND ice.pf_class_level5 = 'Corporate' AND ( p.fundcode LIKE 'A%' OR p.fundcode LIKE 'F%' OR p.fundcode LIKE 'X%' ) ) MERGE INTO [aci_invest].[corfi].[fund_list] AS TARGET USING cte AS Source ON [aci_invest].[corfi].[fund_list].[fundcode] = cte.ACCOUNT_CODE WHEN NOT MATCHED BY TARGET Then Insert into [aci_invest].[corfi].[fund_list] ([list_tag],[fundcode],[added_date],[updated_date],[CorpsAcct],[GuelphAccts]) Values ('cobra_pos01' ,'cte.ACCOUNT_CODE' ,'2018-01-19 16:29:00','2018-01-19 16:29:00' ,'1','[GuelphAccts]')

[emmahyt]

有人会吗。。

[中国风]

这一段用insert ,不能加into

WHEN NOT MATCHED BY TARGET THEN 
INSERT(columnname)VALUES(source.columnname);

[zjcxc]

楼主的语句不完整，存在 更新，不存在 INSERT， 这两个操作你要明确写清楚就行的，参考如下 MERGE INTO [aci_invest].[corfi].[fund_list] AS TARGET USING cte AS Source ON [aci_invest].[corfi].[fund_list].[fundcode] = cte.ACCOUNT_CODE WHEN NOT MATCHED BY TARGET THEN insert(xx,xx) values(xx,xx) WHEN MATCHED THEN UPDATE SET xx=xx ;

[emmahyt]

有些value 是指定的 有些是从表2 获得 这个怎么搞呀，，