thanks for that. I needed a term. bingo!! 🙂

correction, using the WHEN NOT MATCHED THEN INSERT portion of the MERGE statement, I want to insert values into the identity column.

I quote Dave Pinal: "One of the most important advantages of MERGE statement is that the entire data are read and processed only once. In earlier versions, three different statements...

I have found this forum to be a big part of my morale support and I agree. Prominent members of the SQL DBA/Dev community are here, helping completely anonymous beginners...

Well, as I said, those tables are just quick mock ups to illustrate my issue, and do not reflect the naming convention or design practices of the development team where...

OK, thank you.

I found the problem. I had an the order of column names in the insert statment incorrectly aligned with the values I was inserting.

Yes, on varchar columns.

Thank...

Ok, found that in my INSERT statements I had the column names and values mismatched (wrong order) corrected order to align and am on to bigger better errors.

Really stumped. Trying to think of what other tests I could use to troubleshoot.

Datatypes and sizes of straight accross data migration are compatible.

In the case of CASE statements, there are...

OK, please excuse the fact the the problem I am hvig with 'real' merge statement is now different.

When I run the real statement I get the below error:

Msg 8152, Level...

Thank you. It did populate, didn't it?! :doze:

I will now try with the none-pseudocode.

OK, Ninja, understood about edit.

Todd, I was going to use that SELECT statement within a CASE statement (inside USING clause).

I was foolish with my question, however, because...

the answer is, to make the column = NULL (within the select statement). This will populate the column with null values.

Simple as that.

I found problem. I needed to include the variable in both places where the 'not matched' and 'matched' conditions are specified.

EG:

create procedure testsp_get_fYear

(

@year smallint

)

as

merge new as T

using old as...

thanks. yes, the first link to msft is the one I was referring to. Thanks for Pinal's, I'll take a look.

thank you.

Your solution is similar to one I tried to follow here: I have used this and found duplicates. however, there are so many, I don't know how...