Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on May 5th, 2012  |  syntax: None  |  size: 2.68 KB  |  hits: 12  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. What's wrong with this T-SQL MERGE statement?
  2. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempUpsert]') )
  3. drop table TempUpsert;
  4.  
  5. CREATE TABLE [dbo].[TempUpsert](
  6.       [FirstName] [varchar](200) NOT NULL,
  7.       [LastName] [varchar](200) NOT NULL,
  8.       [Score] [int] NOT NULL
  9. ) ON [PRIMARY] ;
  10.  
  11. CREATE TABLE [dbo].[Sales](
  12.       [FullName] [varchar](200) NOT NULL,
  13.       [LastName] [varchar](200) NOT NULL,
  14.       [FirstName] [varchar](200) NOT NULL,
  15.       [lastUpdated] [date] NOT NULL,
  16. CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
  17. (
  18.       [FullName] ASC
  19. )
  20.  
  21. ---- PROC
  22.  
  23. CREATE PROCEDURE  [dbo].[sp_MoveFromTempUpsert_to_Sales]
  24. (@HashMod int)
  25. AS
  26. BEGIN
  27.       -- SET NOCOUNT ON added to prevent extra result sets from
  28.       -- interfering with SELECT statements.
  29.       SET NOCOUNT ON;
  30.  
  31. MERGE Sales AS trget
  32.     USING (
  33.  
  34.     SELECT
  35. --- Edit: Thanks to Mikal added DISTINCT
  36. DISTINCT
  37.             FirstName, LastName , [Score], LastName+'.'+FirstName  AS FullName
  38.     FROM TempUpsert AS ups) AS src (FirstName, LastName, [Score], FullName)
  39.  
  40.     ON
  41.     (
  42.             src.[Score] = @hashMod
  43.     AND
  44.             trget.FullName=src.FullName
  45.     )
  46.  
  47.     WHEN MATCHED
  48.         THEN
  49.  
  50.         UPDATE SET trget.lastUpdated = GetDate()
  51.  
  52.       WHEN NOT MATCHED
  53.             THEN        INSERT   ([FullName], [LastName], [FirstName], [lastUpdated])
  54.       VALUES (FullName, src.LastName, src.FirstName, GetDate())
  55.  
  56.    OUTPUT $action, Inserted.*, Deleted.* ;
  57.       --print @@rowcount
  58.  
  59. END
  60.  
  61. GO
  62.  
  63. ---  Insert dummie data
  64.  
  65. INSERT INTO TempUpsert (FirstName, LastName, Score)
  66. VALUES ('John','Smith',2);
  67.  
  68.  
  69. INSERT INTO TempUpsert (FirstName, LastName, Score)
  70. VALUES ('John','Block',2);
  71.  
  72.  
  73. INSERT INTO TempUpsert (FirstName, LastName, Score)
  74. VALUES ('John','Smith',2); --make multiple on purpose
  75.  
  76. ----- EXECUTE PROC
  77. GO
  78.  
  79.  
  80. DECLARE     @return_value int
  81.  
  82. EXEC  @return_value = [dbo].[sp_MoveFromTempUpsert_to_Sales]
  83.             @HashMod = 2
  84.  
  85. SELECT      'Return Value' = @return_value
  86. GO
  87.        
  88. MERGE Sales AS trget
  89.     USING (
  90.  
  91.     SELECT     FirstName, LastName, Score, LastName + '.' + FirstName AS FullName
  92.     FROM         TempUpsert AS ups
  93.     WHERE Score = @hashMod  
  94.     GROUP BY FirstName, LastName, Score, LastName + '.' + FirstName
  95.  
  96.     ) AS src (FirstName, LastName, [Score], FullName)
  97.  
  98.  
  99.     ON
  100.     (
  101.     --        src.[Score] = @hashMod
  102.     --AND
  103.             trget.FullName=src.FullName
  104.     )
  105.  
  106.     WHEN MATCHED
  107.         THEN
  108.  
  109.         UPDATE SET trget.lastUpdated = GetDate()
  110.  
  111.  
  112.       WHEN NOT MATCHED
  113.             THEN        INSERT   ([FullName], [LastName], [FirstName], [lastUpdated])
  114.       VALUES (FullName, src.LastName, src.FirstName, GetDate())
  115.  
  116.    OUTPUT $action, Inserted.*, Deleted.* ;
  117.       --print @@rowcount
  118.  
  119. END
create a new version of this paste RAW Paste Data