Merge语句每次运行时为什么会将所有数据插入目标表中？

huangapple
go评论104阅读模式
英文:

Why merge statement inserts all the data into the target table each time it runs?

问题

I have these two table with same schema and records on different server and databases.

If the record in source table gets added or modified then it should be reflected in the target table as well but for some weird reasons it inserts all the rows in the target table each time.

I just want it to insert if a new row gets added in the source or modified in the source.

Code:

  1. MERGE INTO dbo.CountrySourceType AS target
  2. USING (
  3.     SELECT Id = Id * (- 1)
  4.         , Name
  5.         , (
  6.             SELECT Id
  7.             FROM country c
  8.             WHERE ISNULL(c.Name, '') = (
  9.                     SELECT ISNULL(cs.Name, '') Name
  10.                     FROM [192.xxx.x.xxx].WebPortal.dbo.Country cs
  11.                     WHERE cs.Id = cst.CountryId
  12.                     )
  13.                 AND ISNULL(c.ContinentName, '') = (
  14.                     SELECT ISNULL(cs.ContinentName, '') ContinentName
  15.                     FROM [192.xxx.x.xxx].WebPortal.dbo.Country cs
  16.                     WHERE cs.Id = cst.CountryId
  17.             )
  18.     ) CountryId
  19.     FROM [192.xxx.x.xxx].WebPortal.dbo.CountrySourceType cst
  20.     WHERE CountryId = cst.CountryId
  21.     ) AS source
  22.     ON source.Id = target.Id
  23. WHEN NOT MATCHED BY target
  24.     THEN
  25.         INSERT (
  26.             [Name],
  27.             [CountryId]
  28.             )
  29.         VALUES (
  30.              source.Name
  31.             ,source.CountryId
  33.             );

I tried it using the merge and narrowed down using the where clause but still it doesn't.

英文:

I have these two table with same schema and records on different server and databases.

If the record in source table gets added or modified then it should be reflected in the target table as well but for some weird reasons it inserts all the rows in the target table each time.

I just want it to insert if new row gets added in the source or modified in the source.

Code:

  1. MERGE INTO dbo.CountrySourceType AS target
  2. USING (
  3. 				SELECT Id = Id * (- 1)
  4. 					, Name
  5. 					, (
  6. 						SELECT Id
  7. 						FROM country c
  8. 						WHERE ISNULL(c.Name, '') = (
  9. 								SELECT ISNULL(cs.Name, '') Name
  10. 								FROM [192.xxx.x.xxx].WebPortal.dbo.Country cs
  11. 								WHERE cs.Id = cst.CountryId
  12. 								)
  13. 							AND ISNULL(c.ContinentName, '') = (
  14. 								SELECT ISNULL(cs.ContinentName, '') ContinentName
  15. 								FROM [192.xxx.x.xxx].WebPortal.dbo.Country cs
  16. 								WHERE cs.Id = cst.CountryId
  17. 						)
  18. 				) CountryId
  19. 				FROM [192.xxx.x.xxx].WebPortal.dbo.CountrySourceType cst
  20. 				WHERE CountryId = cst.CountryId
  21. 				) AS source
  22. 				ON source.Id = target.Id
  23. 			WHEN NOT MATCHED BY target
  24. 				THEN
  25. 					INSERT (
  26. 						[Name],
  27. 						[CountryId]
  28. 						)
  29. 					VALUES (
  30. 						 source.Name
  31. 						,source.CountryId
  32. 		
  33. 						);

I tried it using the merge and narrowed down using the where clause but still it doesn't.

答案1

得分: 0

尽量避免MERGE。通常，您可能会遇到性能问题（以及其他问题）。您可以简单地将source添加到临时表中，然后自行执行更新和插入操作。

  1. DROP TABLE IF EXISTS #temp_table;
  3. SELECT Id = Id * (-1)
  4.       , Name
  5.       , (
  6.         SELECT Id
  7.         FROM country c
  8.         WHERE ISNULL(c.Name, '''') = (
  9.                 SELECT ISNULL(cs.Name, '''') Name
  10.                 FROM [192.xxx.x.xxx].WebPortal.dbo.Country cs
  11.                 WHERE cs.Id = cst.CountryId
  12.                 )
  13.             AND ISNULL(c.ContinentName, '''') = (
  14.                 SELECT ISNULL(cs.ContinentName, '''') ContinentName
  15.                 FROM [192.xxx.x.xxx].WebPortal.dbo.Country cs
  16.                 WHERE cs.Id = cst.CountryId
  17. 				)
  18. 		) CountryId
  20. INTO #temp_table
  22. FROM [192.xxx.x.xxx].WebPortal.dbo.CountrySourceType cst
  23. WHERE CountryId = cst.CountryId
  25. UPDATE dbo.CountrySourceType 
  26. SET name = B.name
  27.    ,County_id = B.CountryId
  28. FROM dbo.CountrySourceType  A
  29. INNER JOIN #temp_table B
  30. 	ON A.[Id] = B.[Id];
  33. INSERT INTO dbo.CountrySourceType ([Name],[CountryId])
  34. SELECT A.name, A.CountryId
  35. FROM #temp_table A
  36. LEFT JOIN dbo.CountrySourceType B
  37. 	ON A.[Id] = B.[Id]
  38. WHERE B.[Id] IS NULL;
  40. INSERT INTO dbo.CountrySourceType ([Name],[CountryId])
  41. SELECT A.name, A.CountryId
  42. FROM #temp_table A
  43. LEFT JOIN dbo.CountrySourceType B
  44.     ON A.[Name] = B.[Name]
  45. 	AND A.[CountryId] = B.[CountryId]
  46. WHERE B.[Id] IS NULL;
英文:

Try to avoid MERGE. Generally, you may have performances issues (among the other issues). You can simply add the source in a temporary table and just perform the update and insert by yourself.

  1. DROP TABLE IF EXISTS #temp_table;
  3. SELECT Id = Id * (- 1)
  4.       , Name
  5.       , (
  6.         SELECT Id
  7.         FROM country c
  8.         WHERE ISNULL(c.Name, '') = (
  9.                 SELECT ISNULL(cs.Name, '') Name
  10.                 FROM [192.xxx.x.xxx].WebPortal.dbo.Country cs
  11.                 WHERE cs.Id = cst.CountryId
  12.                 )
  13.             AND ISNULL(c.ContinentName, '') = (
  14.                 SELECT ISNULL(cs.ContinentName, '') ContinentName
  15.                 FROM [192.xxx.x.xxx].WebPortal.dbo.Country cs
  16.                 WHERE cs.Id = cst.CountryId
  17. 				)
  18. 		) CountryId
  20. INTO #temp_table
  22. FROM [192.xxx.x.xxx].WebPortal.dbo.CountrySourceType cst
  23. WHERE CountryId = cst.CountryId
  25. UPDATE dbo.CountrySourceType 
  26. SET name = B.name
  27.    ,County_id = B.CountryId
  28. FROM dbo.CountrySourceType  A
  29. INNER JOIN #temp_table B
  30. 	ON A.[Id] = B.[Id];
  33. INSERT INTO dbo.CountrySourceType ([Name],[CountryId])
  34. SELECT A.name, A.CountryId
  35. FROM #temp_table A
  36. LEFT JOIN dbo.CountrySourceType B
  37. 	ON A.[Id] = B.[Id]
  38. WHERE B.[Id] IS NULL;
  1. INSERT INTO dbo.CountrySourceType ([Name],[CountryId])
  2. SELECT A.name, A.CountryId
  3. FROM #temp_table A
  4. LEFT JOIN dbo.CountrySourceType B
  5.     ON A.[Name] = B.[Name]
  6. 	AND A.[CountryId] = B.[CountryId]
  7. WHERE B.[Id] IS NULL;

huangapple
  • 本文由 发表于 2023年4月11日 13:58:13
  • 转载请务必保留本文链接：https://go.coder-hub.com/75982773.html
  • sql
  • sql-server
  • sql-server-2019
  • stored-procedures
  • t-sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定