0

I'm new to SQL, and trying to make sense of the Microsoft documentations for using the MERGE command.

I found a question here related to what I am doing and I like this answer, however when I go to replicate the code in my own table, I have an error.

I made a test table called "First_TEST", Column 1 is named TestCOL1 and has a type text.
In case it matters, the other 4 columns are:
TestCOL2 -type is nchar(10)
TestCOL3 -type is smallint
TestCOL4 -type is bit
TestCOL5 -type is text
(I was playing around with different types as I learned)

My code is as follows:

MERGE INTO [dbo].[First_TEST] WITH (HOLDLOCK) AS target 
USING (SELECT 'TEST' as TestCOL1) AS SOURCE 
(TestCOL1) 
ON (target.TestCOL1 = SOURCE.TestCOL1) 
WHEN MATCHED THEN UPDATE SET TESTCOL4 = 0 
WHEN NOT MATCHED THEN INSERT VALUES (SOURCE.TestCOL1,'2','3','1','LAST');

When I run this code, I get the following error:

Msg 402, Level 16, State 1, Line 4
The data types text and varchar are incompatible in the equal to operator.

It seems that target.TestCOL1 is text, as the table is, but SOURCE.TestCOL1 is defaulting to varchar?

I'm not fluent in SQL, so I'm not sure how to add in a line that would tell SOURCE.TestCOL1 to be read as text, so the comparison can work. I tried AS SOURCE (TestCOL1 as text) but that is giving me a syntax error.

Also in case it changes anything:
For my actual table I will need to compare just one text column to see if it is a duplicate or not,
Update two different columns if it is a duplicate,
Or insert all 20 columns if it is not a duplicate.

hammythepig
  • 947
  • 2
  • 8
  • 30
  • select * from #temp_1 a inner join #temp_2 b on cast(a.COL1 as varchar(10)) = b.COL1 – junketsu Sep 18 '19 at 19:09
  • The text datatype has been deprecated for 15 years - no one should be using it at this point. Change your table to use varchar(max). – SMor Sep 18 '19 at 19:12

1 Answers1

1

Convert the TEXT Type to VARCHAR(MAX) like below Statement

 MERGE INTO [dbo].[First_TEST] WITH (HOLDLOCK) AS target 
    USING (SELECT 'TEST' as TestCOL1) AS SOURCE 
    (TestCOL1) 
    ON (Cast(target.TestCOL1 as varchar(max)) = SOURCE.TestCOL1) 
    WHEN MATCHED THEN UPDATE SET TESTCOL4 = 0 
    WHEN NOT MATCHED THEN INSERT VALUES (SOURCE.TestCOL1,'2','3','1','LAST');
Liaqat Kundi
  • 71
  • 6