December 7, 2016 at 7:33 pm
Ok I am merging one database to another. Each table has a dynamic statement.
All but one table is working. The statement below (and you don't really need the statement I don't think) will throw an error, below; when executed within my system. A PROC calls the dynamic statement much like this:
EXEC sp_ExecuteSQL @Statement
I have also tried
EXEC (@Statement)
Both result in the following error:
Msg 50000, Level 14, State 1, Procedure ETLMergeData, Line 357
Violation of UNIQUE KEY constraint 'UX_ConfigTransactionTypeProperty'. Cannot insert duplicate key in object 'dbo.ConfigTransactionTypeProperty'. The duplicate key value is (AllowInputDetailTime, <NULL>).
Ok fine right. Take a look at the table and see what you missed..... NOTHING. The WHERE clause covers the Unique index
The statement is at the end of this.
So I print the statement to screen so as they are executed I see what was grabbed.. There is it... All pretty like... I copy / paste the statement that just threw the error into ssms; hit F5.. Guess what happens? 675 rows copied, no error!!
And thoughts?
Here is the statement. BatchID change with each run. The JOIN statements are correct they are re-mapping the FK columns. In the case of Identity those will get renumbered so I need to map old value to new value. This is not an issue. There are 800+ tables in the DB. The statements are dynamically generated for all and this is the only problem table. Very strange.
INSERT INTO [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigTransactionTypeProperty] ([ConfigTransactionTypeNodeId], [ConfigTransactionTypePropertyGUID], [Property], [Value] ,ImportIdentity, GUIDIdentity) SELECT FK1.DestinationID, st.[ConfigTransactionTypePropertyGUID], st.[Property], st.[Value], st.ConfigTransactionTypePropertyID, '0000' FROM [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigTransactionTypeProperty] st LEFT JOIN [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigTransactionTypeNode] as FK1Table on FK1Table.ConfigTransactionTypeNodeId = st.ConfigTransactionTypeNodeId LEFT JOIN [ETL].[dbo].[ETLCorrelation] FK1 on FK1.SourceID = ST.ConfigTransactionTypeNodeId AND FK1.SourceTable = 'ConfigTransactionTypeNode' AND FK1.BatchID = 6430 AND FK1.SourceID = FK1.DestinationID AND st.ConfigTransactionTypeNodeId NOT IN (SELECT SourceID FROM ETLCorrelation WHERE DestinationTable = 'ConfigTransactionTypeNode' AND SourceID <> DestinationID) WHERE NOT EXISTS (SELECT 1 FROM [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigTransactionTypeProperty] co
WHERE (st.Property + coalesce(cast(st.ConfigTransactionTypeNodeId as varchar), '0')) = (Property + coalesce(cast(co.ConfigTransactionTypeNodeId as varchar), '0') ))
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 7, 2016 at 8:45 pm
This was removed by the editor as SPAM
December 7, 2016 at 11:14 pm
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.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 8, 2016 at 7:29 am
I added the alias as you suggested, same exact result. While it certainly should be aliased I had a feeling doing so would not result in a different outcome. There are 800+ tables being merged. the logic that builds the statements is the same for all of them so they would all be missing the alias. This is the only table failing.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 8, 2016 at 8:29 pm
This was removed by the editor as SPAM
December 8, 2016 at 8:50 pm
Thanks for the message.
First let me say this is a commercial software package, I DID NOT DESIGN THE DB.
Yes the WHERE clause represents the index (there is another statement that DOES work called by my system) and this one works manually.
And YES this Unique index allows NULL value on that column and therein I coalesce this. I will look at that part again. And again I did not design a DB that allows a NULL value within a unique index!!!I hardly ever allow NULL regardless let alone a move like that. You should see some of the stuff I have accounted for. Tables with no PK and they enforce PK within the .net app. So to merge I have a table (ImpliedPK) and one for foreign keys as well where not defined in the DB but enforced in code. I could go on but I think you get the point.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply