Multiple Merge with Multiple Rows

What I am trying to accomplish in one shot is update else insert data into 2 tables matching a primary key on one table, and the secondaryID on another. The collection of the initial data will have multiple rows that have the same secondaryID. I want most of the latest data. If it can be added to allow me to do functions on update at the same time, that would be awesome.

USE [TestDB]
GO

--DELETING CREATED FUNCTIONS, TABLES, AND TYPES
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_Proc_1' AND [TYPE] IN (N'P',N'PC'))
  BEGIN
      DROP PROCEDURE dbo.sp_Proc_1;
  END

IF EXISTS (SELECT 1 FROM sys.types WHERE is_user_defined = 1 AND is_table_type = 1 AND name = 'My_Table_Type_1')
    BEGIN
        DROP TYPE dbo.My_Table_Type_1;
    END

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Table_Data'))
BEGIN
    DROP TABLE Table_Data;
END

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Table_1'))
BEGIN
    DROP TABLE Table_1;
END

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Table_2'))
BEGIN
    DROP TABLE Table_2;
END

/****** Object:  Table [dbo].[Table_1]    Script Date: 6/28/2015 6:15:00 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
--CREATE SAMPLE TABLE WITH DATA
CREATE TABLE [dbo].[Table_Data](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [secondaryID] [varchar](50) NULL,
    [col1] [varchar](50) NULL,
    [col2] [varchar](50) NULL,
    [col3] [int] NULL
 CONSTRAINT [PK_Table_Data] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO Table_Data (secondaryID,col1,col2,col3) VALUES ('1234','Mickey','Magic Kingdom',1);
INSERT INTO Table_Data (secondaryID,col1,col2,col3) VALUES ('1234','Goofy','Epcot',1);
INSERT INTO Table_Data (secondaryID,col1,col2,col3) VALUES ('1234','Minnie','Disney',2);
INSERT INTO Table_Data (secondaryID,col1,col2,col3) VALUES ('5678','Toy Story','Universal Studios',4);
INSERT INTO Table_Data (secondaryID,col1,col2,col3) VALUES ('5678','Willie','Sea World',5);

--FIRST TABLE FOR DATA TO BE COPIED INTO BY ID
CREATE TABLE [dbo].Table_1(
    [id] [int] NULL,
    [secondaryID] [varchar](50) NULL,
    [col1] [varchar](50) NULL,
    [col2] [varchar](50) NULL,
    [col3] [int] NULL
) ON [PRIMARY]
GO

--SECOND TABLE THAT HAS SECONDARY ID AS "UNIQUE ID"
CREATE TABLE [dbo].Table_2(
    [secondaryID] [varchar](50) NULL,
    [col1] [varchar](50) NULL,
    [col2] [varchar](50) NULL,
    [col3] [int] NULL
) ON [PRIMARY]
GO

--CREATE TABLE TYPE (FOR APPLICATION)
CREATE TYPE dbo.My_Table_Type_1 AS TABLE(
    [id] [int] NULL,
    [secondaryID] [varchar](50) NULL,
    [col1] [varchar](50) NULL,
    [col2] [varchar](50) NULL,
    [col3] [int] NULL
)
GO

--CREATE STORED PROCEDURE FOR MULTI MERGE AND MULTI ROW (UPDATE ELSE INSERT)
CREATE PROCEDURE dbo.sp_Proc_1
@myTBL dbo.My_Table_Type_1 READONLY
AS
BEGIN
    DECLARE @myTBL2 dbo.My_Table_Type_1;
    INSERT INTO @myTBL2
    SELECT * FROM @myTBL;

    MERGE dbo.Table_1 AS Target
    USING @myTBL AS Source
    ON Target.ID = Source.ID
    WHEN MATCHED THEN
        UPDATE SET 
        Target.secondaryID = Source.secondaryID,
        Target.col1 = Source.col1,
        Target.col2 = Source.col2,
        Target.col3 = Source.col3
    WHEN NOT MATCHED THEN
        INSERT
        (id,secondaryID,col1,col2,col3) 
        VALUES
        (Source.id,Source.secondaryID,Source.col1,Source.col2,Source.col3);  

    MERGE dbo.Table_2 AS Target
    --gets latest data
    USING(SELECT * FROM (SELECT *, SUM(col3) OVER(PARTITION BY secondaryID) sumcol3, ROW_NUMBER() OVER(PARTITION BY secondaryID ORDER BY id DESC) rn FROM @myTBL)t WHERE rn = 1) AS Source
    --USING @myTBL AS Source
    ON Target.secondaryID = Source.secondaryID
    WHEN MATCHED THEN
        UPDATE SET
        Target.col1 = Source.col1,
        Target.col2 = Source.col2,
        --Target.col3 = Target.col3 + Source.col3
        Target.col3 = sumcol3
        --Target.col3 = 7 --<---------------THIS DOES NOT GET SAVED
    WHEN NOT MATCHED THEN
        INSERT
        (secondaryID,col1,col2,col3)
        VALUES
        (Source.secondaryID,Source.col1,Source.col2,Source.col3);
END
GO

SET ANSI_PADDING OFF
GO

--GET DATA AND EXECUTE PROC (SIMULATE APPLICATION)
DECLARE @tbl My_Table_Type_1

INSERT INTO @tbl
SELECT * FROM Table_Data;

EXECUTE sp_Proc_1 @myTBL = @tbl

--FIRST SELECT IS CORRECT, should list every value
SELECT * FROM Table_1;

--SECOND SELECT IS ***NOT*** CORRECT
SELECT * FROM Table_2;
--I WANT THIS DATA TO SHOW 2 ITEMS
--  1234    Minnie  Disney      4
--  5678    Willie  Sea World   9

  • Drop a localdb database from VS package manager
  • Stored Procedure Versioning
  • SQL records with common ID - update all with single user defined function call
  • ALTER DATABASE SET SINGLE_USER WITH NO_WAIT waits 20 seconds
  • Connecting to MS SQL Server using python on linux with 'Windows Credentials'
  • Delete session from database after it expired?
  • One Solution collect form web for “Multiple Merge with Multiple Rows”

    I think you can accomplish this with window function in using part of merge statement:

    MERGE dbo.Table_2 AS Target
    USING(SELECT * FROM (SELECT *, 
                                SUM(col3) OVER(PARTITION BY secondaryID) sumcol3,
                                ROW_NUMBER() OVER(PARTITION BY secondaryID ORDER BY id DESC) rn
                          FROM @myTBL)t WHERE rn = 1) AS Source
    ....
    UPDATE SET
        Target.col1 = Source.col1,
        Target.col2 = Source.col2,
        Target.col3 = Source.sumcol3
    ...
    WHEN NOT MATCHED THEN
        INSERT
        (secondaryID,col1,col2,col3)
        VALUES
        (Source.secondaryID, Source.col1, Source.col2, Source.sumcol3);
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.