SQL server MERGE 2 source/target tables with inner join

Question

I have a SQL server database that contains 2 tables, these tables are joined by an ID field as follows:

Table 1
ID, Selection, Field1, Field2

Table 2
ID, Field3, Field4

The tables are joined by a foreign key with a cascade on delete constraint so that if a field in table 1 is deleted, all fields in table 2 matching the ID will also be deleted (but you knew that)

I have another stored procedure that pulls out the necessary fields by selection for both tables:

    SELECT [Table2].* FROM [Table2] INNER JOIN [Table1] ON [Table1].[ID] = [Table2].[ID] WHERE [Table1].Selection = @selectionParameter

I would like to update these tables from my c# program. I have previously been doing the following:

DELETE FROM [Table1] WHERE Selection = @selectionParameter

INSERT INTO [Table1] SELECT * FROM @table1Parameter (user defined table type passed in from c#)

INSERT INTO [Table2] SELECT * FROM @table2Parameter

I would now like to make this more streamlined by not forcing a full delete and reinsert of all fields and instead performing a merge.

So far I have the merge functioning on Table1 but can't seem to get the relationship with Table2 right.

Here's what I have so far:

MERGE INTO [Table1] AS target
USING @Table1Parameter AS source
ON target.ID = source.ID
AND target.Selection = @selectionParameter

WHEN NOT MATCHED BY TARGET
    THEN INSERT (ID, Selection, Field1, Field2) VALUES (source.ID, etc)
WHEN NOT MATCHED BY SOURCE AND target.Selection = @selectionParameter
    THEN DELETE;

This is a very similar query to: SQL Server MERGE + Joining other tables

I have tried using the answer from the above and passing the results from the relationship select statement above into a temp table but have had no success.

Can someone help me integrate my second table into this?

edit: Asked to provide the full code:

USE [MyDatabase]
GO
ALTER PROCEDURE [dbo].[MergeTables]

@Selection int, 
@Table1Parameter udtt_1 READONLY,
@table2Parameter udtt_2 READONLY

AS

then the merge statement you see above.

The c# code simply executes a non query using a datatable as a parameter. The c# code is currently fully functional.

Can you provide the full code ?? like **@table2Parameter**, **@selectionParameter** etc... — Krishnraj Rana, Sep 19 '14 at 16:25
In procedure you have passed - **@Selection** and in merge statement you have used - **@selectionParameter** variable. Is it correct ?? — Krishnraj Rana, Sep 19 '14 at 16:51
woops, sorry that was a mistype here, it is the same variable. the name on the lower code should be 'selectionParameter' — Anya Hope, Sep 22 '14 at 07:42

score 0 · Accepted Answer · answered Sep 22 '14 at 10:19

This was the fix, it did involve temp tables after all:

USE [myDatabase]
GO

ALTER PROCEDURE [dbo].[MergeTables]
--declare parameters passed in from c#
--tables coming from c# are edited versions of the SQL tables.
@selectionParameter int, 
@Table1Parameter udtt_1 READONLY,
@table2Parameter udtt_2 READONLY

AS

--merge edits into table 1
MERGE INTO [Table1] AS target
USING @Table1Parameter AS source
ON target.ID = source.ID
AND target.Selection = @selectionParameter

WHEN NOT MATCHED BY TARGET
THEN INSERT (ID, Selection, Field1, Field2) VALUES (source.ID, source.Selection, source.Field1, source.Field2)
WHEN NOT MATCHED BY SOURCE AND target.Selection = @selectionParameter
THEN DELETE;

--define a temp table to hold table 2 editable rows
SELECT [Table2].* INTO T FROM [Table2]
INNER JOIN [Table1] ON [Table1].[ID] = [Table2].[ID] WHERE [Table1].[Selection] = @selectionParameter

--merge edits into temp table
MERGE INTO T AS target 
USING @table2Parameter AS source
ON target.ID = source.ID
WHEN NOT MATCHED BY TARGET
    THEN INSERT (ID, Field3, Field4) VALUES (source.ID, source.Field3, source.Field4)
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

--place edited or new rows into table 2
MERGE INTO [Table2] AS target 
USING T AS source
ON target.ID = source.ID
WHEN NOT MATCHED BY TARGET
    THEN INSERT (ID, Field3, Field4) VALUES (source.ID, source.Field3, source.Field4);

DROP TABLE T;

Please let me know if this needs clarification. It's horribly complex and took me hours! Hope it can help someone else in this situation.

SQL server MERGE 2 source/target tables with inner join

1 Answers1