Hmm. Are you certain? All the other tables move data ok. They all have the same exact rules. The statements are generated by a dynamic system I have been writing for a client.

You give it a source Server, Instance, DB and a target server / instance / db.

It checks the schema of both. if the same it then proceeds to build META tables with all the particulars of each table (So I am not interrogating the sys tables constantly and so I can have my own indexes, and various other criteria. It then proceeds to build statements (4 per table) and then execute them in order of dependency / and in a particular order for the statements for each table..

The thing is this statement fails on a conflict when executed by my proc. If I copy / paste to SSMS and hit F5 it works just fine and the correct data is moved.

So the real mystery is two fold.

1. All the other statements work just fine.

2. This statement is happy happy if run in ssms but throws an error when my process loads the statement into a variable and executes it.

Do I know for sure that the statement is loaded properly and in its entirety? Yep. If I grab it from SQL myself or just copy it from the PRINT of it while my process is run, either case they are the same and in either case they can be executed manually in ssms.