SQL Server – Element with Same Key Already Added Error

sql serversql-server-2012

Good morning community, I am having problems when I run this query from SQL 2018 and Visual basic 2010 shows me the following error "an element with the same key has already been added"

SELECT DESCRIPCION AS nodeText,'DEPA' + CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeKey,''AS nodeParentKey FROM DEPARTAMENTO
UNION ALL
SELECT DESCRIPCION AS nodeText,'PROV' + CAST(IDPROVINCIA AS VARCHAR) AS nodeKey,
'DEPA' + CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeParentKey FROM PROVINCIA
UNION ALL
SELECT DESCRIPCION AS nodeText,'DIST' + CAST(IDUBIGEO AS VARCHAR) AS nodeKey,
'PROV' + CAST(IDPROVINCIA AS VARCHAR) AS nodeParentKey FROM ubigeo

As I can avoid duplicate elements, I think it is with the DISTINCT clause but I have no idea where this clause can go, please support

Best Answer

use UNION instead of UNION ALL in this way:

SELECT DESCRIPCION AS nodeText,'DEPA' + CAST(IDDEPARTAMENTO AS VARCHAR) AS 
nodeKey,''AS nodeParentKey FROM DEPARTAMENTO
UNION ALL
SELECT DESCRIPCION AS nodeText,'PROV' + CAST(IDPROVINCIA AS VARCHAR) AS nodeKey,
'DEPA' + CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeParentKey FROM PROVINCIA
UNION
SELECT DESCRIPCION AS nodeText,'DIST' + CAST(IDUBIGEO AS VARCHAR) AS nodeKey,
'PROV' + CAST(IDPROVINCIA AS VARCHAR) AS nodeParentKey FROM ubigeo

it will remove duplicates

Related Solutions

SQL Server 2005 – Fix Execution Plan Flips Filter and Execute Scalar Error

I'm sure there are dba.se and StackOverflow duplicates, but it was far faster for me to post this link:

https://feedback.azure.com/forums/908035-sql-server/suggestions/32912431-sql-server-should-not-raise-illogical-errors

In short, the Query Optimizer is really free to rewrite the plan as it sees best. Sometimes, it means a column is transformed (CAST -> int) close to retrieval so that it doesn't have to be carried many steps forward before performing the CAST.

The only sure-fire way of preventing the error is to look at the CAST expression itself, e.g.:

cast(CASE WHEN ISNUMERIC(ENTRY_CODE)=1 THEN ENTRY_CODE END as int)

Sql-server – Import Data replaces NULL with 0

Choose to save the package instead of running it right away.
Then edit the dtsx file and on your copy task check "keep nulls".
Then run the dtsx.
I didn't test it but think it will solve your case. enter image description here

Testing the suggestion on the comment on express with advanced services... it worked well:) You create the destination table, set a null default on the column and after importing data with the dtsx you remove the default or set it to whatever you want. dtsx import will replace nulls with the default value of the column when you can't check the "keep nulls".

create table dbo.test (
 a int null
 , other columns ...
 )

alter table dbo.test add constraint x default null for a

--Run Import task here

alter table dbo.test drop constraint x

Best Answer

Related Solutions

SQL Server 2005 – Fix Execution Plan Flips Filter and Execute Scalar Error

Sql-server – Import Data replaces NULL with 0

Related Question