cannot insert data into table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

cannot insert data into table

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-05-04 : 19:14:45

Hi i am trying to insert data into a table which has unique index on step_id...

here the error message i get ,

Cannot insert duplicate key row in object 'dbo.DW_T_EC_TGT' with unique index 'Step_id'.

The statement has been terminated.

my insert stament

Insert INTO DW_T_EC_TGT
(
[CRM_BATCH_QUEUE_NAME],
[EC_JOB_IND],
[EC_JOB_NAME],
[EC_JOB_STEP_NAME],
[EC_PROJECT_NAME] ,
[EC_PROCEDURE_NAME],
[EC_JOB_ID] ,
[EC_STEP_ID] ,
[EC_PROJECT_ID] ,
[EC_LAUNCHED_BY_USER] ,
[EC_CREATED_TIME] ,
[EC_START_TIME] ,
[EC_FINISH_TIME] ,
[EC_RUNNABLE_TIME] ,
[EC_OWNER] ,
[EC_HOST_NAME] ,
[EC_RESOURCE_NAME] ,
[EC_OUTCOME],
[EC_STATUS],
[EC_STEP_INDEX] ,
[EC_START_MILLIS] ,
[EC_FINISH_MILLIS] ,
[EC_RUNNABLE_MILLIS] ,
EC_PARENT_ID,
EC_JOB_PRIORITY ,
[CRM_PHONE_BUILD] ,
[CRM_SUB_SYSTEM],
[CRM_TECHNOLOGY] ,
[CRM_CHIP_GROUP],
[CRM_TARGET_NAME] ,
[CRM_BATCH_ENV] ,
[CRM_BATCH_SERVER_NAME],
[CRM_CLIENT] ,
[CRM_BUILD_SIZE] ,
[CRM_BATCHRECORDID],
[CRM_BUILD_ID] ,
[CRM_BUILT_DT] ,
[CRM_SUBMIT_TIME],
[CRM_START_TIME],
[CRM_END_TIME] ,
[CRM_RUN_GMT_DIFF_SECS],
[CRM_JOB_STATUS] ,
[CRM_SUBMIT_TIME_SECS],
[CRM_START_TIME_SECS],
[CRM_END_TIME_SECS],
[CRM_GMT_DIFF_SECS] ,
[CRM_DAY_LIGHT_FLAG],
[CRM_SITE],
[JOB_TYPE])
(
SELECT

t.[CRM_BATCH_QUEUE_NAME],
t.[EC_JOB_IND],
t.[EC_JOB_NAME],
t.[EC_JOB_STEP_NAME],
t.[EC_PROJECT_NAME] ,
t.[EC_PROCEDURE_NAME],
t.[EC_JOB_ID] ,
t.[EC_STEP_ID] ,
t.[EC_PROJECT_ID] ,
t.[EC_LAUNCHED_BY_USER] ,
t.[EC_CREATED_TIME] ,
t.[EC_START_TIME] ,
t.[EC_FINISH_TIME] ,
t.[EC_RUNNABLE_TIME] ,
t.[EC_OWNER] ,
t.[EC_HOST_NAME] ,
t.[EC_RESOURCE_NAME] ,
t.[EC_OUTCOME],
t.[EC_STATUS],
t.[EC_STEP_INDEX] ,
t.[EC_START_MILLIS] ,
t.[EC_FINISH_MILLIS] ,
t.[EC_RUNNABLE_MILLIS] ,
t.EC_PARENT_ID,
t.EC_JOB_PRIORITY ,
t.[CRM_PHONE_BUILD] ,
t.[CRM_SUB_SYSTEM],
t.[CRM_TECHNOLOGY] ,
t.[CRM_CHIP_GROUP],
t.[CRM_TARGET_NAME] ,
t.[CRM_BATCH_ENV] ,
t.[CRM_BATCH_SERVER_NAME],
t.[CRM_CLIENT] ,
t.[CRM_BUILD_SIZE] ,
t.[CRM_BATCHRECORDID],
t.[CRM_BUILD_ID] ,
t.[CRM_BUILT_DT] ,
t.[CRM_SUBMIT_TIME],
t.[CRM_START_TIME],
t.[CRM_END_TIME] ,
t.[CRM_RUN_GMT_DIFF_SECS],
t.[CRM_JOB_STATUS] ,
t.[CRM_SUBMIT_TIME_SECS],
t.[CRM_START_TIME_SECS],
t.[CRM_END_TIME_SECS],
t.[CRM_GMT_DIFF_SECS] ,
t.[CRM_DAY_LIGHT_FLAG],
t.[CRM_SITE],
t.[JOB_TYPE]
FROM DW_T_EC_TGT_TEMP t
LEFT JOIN DW_T_EC_TGT j ON j.EC_STEP_ID = t.EC_STEP_ID
WHERE j.EC_STEP_ID IS NULL)

Basically having left join where ec_step_id is null should enter the new records ..then why i am getting this error?

Any body please help me...........

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-05 : 01:17:34

It is because DW_T_EC_TGT_TEMP has duplicates in the first place. So you need to eliminate them.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 02:19:42

it may be that you've one to many relationship existing b/w DW_T_EC_TGT_TEMP and DW_T_EC_TGT. so you need determine how exactly you want data to avoid the duplicates.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-05-05 : 03:20:14

In the table DW_T_EC_TGT_TEMP stepID may not be Unique.
thats y u r getting this error.
please check that.
or
It must have more than one null values.

Karthik
http://karthik4identity.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources