October 11, 2011 at 7:02 am
Hi Experts,
I am a newbie to SQL Server 2008. And I am trying to use the merge statement which gives me the following error :
"The Insert Column list used in the MERGE statement cannot contain multi-part identifiers. Use single identifier instead."
I am trying to execute this :
MERGE INTO SURFBI_DASHBOARDS TGT
USING (SELECT 'DASH1' DASHBOARD_NAME, 1 INSTALL_ID) SRC
ON (TGT.DASHBOARD_NAME = SRC.DASHBOARD_NAME
AND TGT.INSTALL_ID = SRC.INSTALL_ID)
WHEN MATCHED THEN
UPDATE SET TGT.EFFECTIVE_START_DATE = NULL,
TGT.EFFECTIVE_END_DATE = NULL,
TGT.UPDATED_BY = 1,
TGT.UPDATION_DATE = GETDATE(),
TGT.COMMENTS = NULL
WHEN NOT MATCHED THEN
INSERT(
TGT.INSTALL_ID,
TGT.DASHBOARD_NAME,
TGT.EFFECTIVE_START_DATE,
TGT.EFFECTIVE_END_DATE,
TGT.CREATED_BY,
TGT.CREATION_DATE,
TGT.UPDATED_BY,
TGT.UPDATION_DATE,
TGT.COMMENTS,
TGT.IS_ENABLED
)
VALUES(1, 'DASH1', NULL, NULL, 0, GETDATE(), NULL, GETDATE(), NULL, 'Y');
What am I doing wrong. The logic for the insertion/ updating is defined in the statement. Please Help.
October 11, 2011 at 7:07 am
have you tried removing the multi-part column identifiers from the insert statement?
eg
TGT.INSTALL_ID
to
INSTALL_ID
October 11, 2011 at 7:34 am
Since it can only insert into the target table, you can't use object.column naming in that context. Just the column names.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 11, 2011 at 7:37 am
After the suggested code change. I am now getting a new error which says "A MERGE statement must be terminated by a semi-colon (;)"
The Modified statement is :
MERGE SURFBI_DASHBOARDS TGT
USING (SELECT 'DASH1' DASHBOARD_NAME, 1 INSTALL_ID) SRC
ON (SRC.DASHBOARD_NAME = TGT.DASHBOARD_NAME AND SRC.INSTALL_ID = TGT.INSTALL_ID)
WHEN MATCHED THEN
UPDATE SET TGT.EFFECTIVE_START_DATE = NULL,
TGT.EFFECTIVE_END_DATE = NULL,
TGT.UPDATED_BY = 1,
TGT.UPDATION_DATE = GETDATE(),
TGT.COMMENTS = NULL
WHEN NOT MATCHED THEN
INSERT(
INSTALL_ID,
DASHBOARD_NAME,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
CREATED_BY,
CREATION_DATE,
UPDATED_BY,
UPDATION_DATE,
COMMENTS,
IS_ENABLED
)
VALUES(1, 'DASH1', NULL, NULL, 0, GETDATE(), NULL, GETDATE(), NULL, 'Y');
What changes do I need to make. Please suggest.
Thanks in Advance. 🙂
October 13, 2011 at 6:18 am
Looks correct to me, and I don't get any error on it when I check it in SSMS. I can't run it, because I don't have the tables, but it looks okay.
Maybe you selected the command but not the semicolon at the end?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2011 at 1:23 pm
Remove TGT. from Merge statement inside when matched.
Thanks
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply