SQL Server 2017中的合并

Question

这是我的查询，我想将数据从源合并到目标(我是SQL Server 的初学者

DECLARE @T TABLE(NoContratAdhesion char(8) );
DECLARE @rqt as nvarchar (800000)
SET = 'SELECT * 
FROM infocentre.[dbo].[TCtrCollRG] as RG
INNER JOIN SSIS_Temp.dbo.TLID_ADH_RG1_HUM AS RG1 ON RG.NoContratAdhesion = RG1.NoContratAdhesion'
USING (SELECT * FROM SSIS_Temp.dbo.Tmp_CollRG_sans_cle_HUM 
WHERE Flag_doublons <> '1') AS SOURCE
ON (TARGET.Nocontratadhesion = SOURCE.NoContratAdhesion
AND TARGET.NoAvenant = SOURCE.NoAvenant  
AND TARGET.CodeRG=SOURCE.CodeRG)  
WHEN NOT MATCHED BY TARGET  
THEN 
INSERT ([NoContratAdhesion], [NoAvenant], [NoAdherent],
[CodeProduitCible], [CodeRG], [Optionnel], [Retenu],
[Retarde], [DateDebutValiditeRG], [DateFinValiditeRG],
[DateMajRG], [DateInsertPl], [DateMajPl], Date_CHARG_SIAD, FLAG_DELTA)
VALUES (SOURCE.[NoContratAdhesion], SOURCE.[NoAvenant], NULL, 
SOURCE.[CodeProduitCible], SOURCE.[CodeRG], SOURCE.[Optionnel], SOURCE.[Retenu],
SOURCE.[Retarde], SOURCE.[DateDebutValiditeRG], SOURCE.[DateFinValiditeRG],
SOURCE.[DateMajRG], SOURCE.[DateInsertPl], SOURCE.[DateMajPl], GETDATE(), 'I')
WHEN MATCHED
THEN UPDATE SET
TARGET.[NoContratAdhesion]=SOURCE.[NoContratAdhesion]
,TARGET.[NoAvenant]=SOURCE.[NoAvenant]
,TARGET.[NoAdherent]=NULL
,TARGET.[CodeProduitCible]=SOURCE.[CodeProduitCible]
,TARGET.[CodeRG]=SOURCE.[CodeRG]
,TARGET.[Optionnel]=SOURCE.[Optionnel]
,TARGET.[Retenu]=SOURCE.[Retenu]
,TARGET.[Retarde]=SOURCE.[Retarde]
,TARGET.[DateDebutValiditeRG]=SOURCE.[DateDebutValiditeRG]
,TARGET.[DateFinValiditeRG]=SOURCE.[DateFinValiditeRG]
,TARGET.[DateMajRG]=SOURCE.[DateMajRG]
,TARGET.[DateInsertPl]=SOURCE.[DateInsertPl]
,TARGET.[DateMajPl]=SOURCE.[DateMajPl]
, TARGET.Date_CHARG_SIAD =getdate()
,TARGET.FLAG_DELTA='M'
WHEN NOT MATCHED BY SOURCE
THEN DELETE 
OUTPUT Source.NoContratAdhesion
INTO @T;             
DELETE infocentre.[dbo].[TCtrCollRG]
WHERE NoContratAdhesion     in (SELECT NoContratAdhesion
FROM @T);
select count(*) from infocentre.[dbo].[TCtrCollRG]

如果目标中没有，我想删除数据，如果存在，我想更新，我尝试了这个查询，但有错误。

你能帮我吗？

Answer 1

nvarchar不允许长度为800000。set命令没有描述要设置的变量。您没有merge子句来指示您的目标表是什么。

至少，您需要更改以下内容：

DECLARE @T TABLE(NoContratAdhesion char(8) );
DECLARE @rqt as nvarchar (800000)
set= 'SELECT * from infocentre.[dbo].[TCtrCollRG] as RG
INNER JOIN SSIS_Temp.dbo.TLID_ADH_RG1_HUM AS RG1 on RG.NoContratAdhesion=RG1.NoContratAdhesion'
USING (SELECT *  from  SSIS_Temp.dbo.Tmp_CollRG_sans_cle_HUM where Flag_doublons <> '1')   AS SOURCE
ON   (TARGET.Nocontratadhesion = SOURCE.NoContratAdhesion
and TARGET.NoAvenant = SOURCE.NoAvenant  and TARGET.CodeRG=SOURCE.CodeRG)  

进入这个：

DECLARE @T TABLE (NoContratAdhesion char(8));
DECLARE @rqt nvarchar(4000);
set @rqt = '
SELECT * from infocentre.[dbo].[TCtrCollRG] as RG
INNER JOIN SSIS_Temp.dbo.TLID_ADH_RG1_HUM AS RG1 on RG.NoContratAdhesion=RG1.NoContratAdhesion
';
merge   @T target
USING   (SELECT * from  SSIS_Temp.dbo.Tmp_CollRG_sans_cle_HUM where Flag_doublons <> '1')   AS SOURCE
ON      TARGET.Nocontratadhesion = SOURCE.NoContratAdhesion
and     TARGET.NoAvenant = SOURCE.NoAvenant  and TARGET.CodeRG=SOURCE.CodeRG  

经过粗略的扫描，我不认为你的其他逻辑有什么大问题。

这是假设您实际上正试图从实际表合并到@T中，而不是相反。否则切换merge和using语句。