Merge not inserting. No error

Question

Can someone tell me why this insert is failing but not giving me an error either? How do I fix this?

merge table1 as T1
using(select p.1,p.2,p.3,p.4,p.5 from @parameters p
inner join table1 t2
on p.1 = t2.1
and p.2 = t2.2
and p.3 = t2.3
and p.4 = t2.4) as SRC on SRC.2 = T1.2
when not matched then insert (p.1,p.2,p.3,p.4,p.5) 
values (SRC.1,SRC.2,SRC.3,SRC.4,SRC.5)
when matched then update set t1.5 = SRC.5;

The T1 table is currently empty so nothing can match. The parameters table does have data in it. I simply need to modify this merge so that it checks all 4 fields before deciding what to do.

score 0 · Answer 1 · edited May 23 '17 at 12:12

0

You can't select from a variable: from @parameters

See the following post: Using a variable for table name in 'From' clause in SQL Server 2008

edited May 23 '17 at 12:12

Community

1
1

answered Oct 27 '12 at 00:05

Steve's a D

3,801
10
39
60

I tried a temp table rather than a variable. Same result. Does merge only work on true tables in the same database? – Matt Oct 27 '12 at 17:59
nope it can work across databases as long as you use the fully qualified table name – Steve's a D Oct 27 '12 at 18:07

score 0 · Answer 2 · answered Oct 27 '12 at 18:34

Actually, you can use a variable table. Check it out:

  MERGE Target_table AS [Target]
  USING @parameters AS [Source]
  ON (

        [Target].col1 = [Source].col1
        AND [Target].col2 = [Source].col2
        AND [Target].col3 = [Source].col3
        AND [Target].col4 = [Source].col4
        )
  WHEN NOT MATCHED BY TARGET
  THEN INSERT (col1,col2,col3,col4,col5)
  VALUES (
              [Source].col1
              ,[Source].col2
              ,[Source].col3
              ,[Source].col4
              ,[Source].col5
                  )
  WHEN MATCHED
  THEN UPDATE SET [Target].col5 = [Source].col5;

Did this fix the problem or is this just a refactor of the original? If you use a select statement as your source and it doesn't match any records the merge will result in nothing inserted and no error message. So in your original question, if you pulled that source's select statement out and ran it directly, it should return no rows. — Greg Biles, Feb 01 '13 at 14:19

Merge not inserting. No error

2 Answers2