August 5, 2011 at 3:06 am
Hello,
I have never posted on a forum before but I think this could defeat me. I have pasted my code below and I am getting an error on the multi-part identifier in Bold.
I have two questions:
1) Can I use a third table (tblInstanceNew) to Insert/Update data into?
2) Can you provide the code which will enable me to do so?
MERGE tblInstances AS TARGET
USING tblInstanceImport AS Source
ON (Target.InstanceName= Source.InstanceName)
WHEN MATCHED THEN
UPDATE
SET [Home] = Source.[Home]
,[Version] = Source.[Version]
,[DateCollected] = GetDate();
WHEN NOT MATCHED BY TARGET THEN
UPDATE
SET tblInstanceNew.[ServerName] = Source.[Nodename]
,tblInstanceNew.[InstanceName] = Source.[WAS_Instance]
,tblInstanceNew.[DateCollected] = GetDate();
August 5, 2011 at 3:09 am
Hi
I think all you're missing is the schema, like this:
SET tblInstanceNew.dbo.[ServerName] = Source.[Nodename]
,tblInstanceNew.dbo.[InstanceName] = Source.[WAS_Instance]
,tblInstanceNew.dbo.[DateCollected] = GetDate();
Kind regards
Lars Søe Mikkelsen
August 5, 2011 at 3:17 am
Hello,
Thank you for your quick response but unfortunately this has not worked, I am still getting the same error 'The multi-part identifier could not be bound'.
MERGE tblInstances AS TARGET
USING tblInstanceImport AS Source
ON (Target.InstanceName= Source.InstanceName)
WHEN MATCHED THEN
UPDATE
SET [Home] = Source.[Home]
,[Version] = Source.[Version]
,[DateCollected] = GetDate();
WHEN NOT MATCHED BY TARGET THEN
UPDATE
SET tblInstanceNew.dbo.[ServerName] = Source.[Nodename]
,tblInstanceNew.dbo.[InstanceName] = Source.[WAS_Instance]
,tblInstanceNew.dbo.[DateCollected] = GetDate();
August 5, 2011 at 3:20 am
Hi again
try this:
MERGE tblInstances AS TARGET
USING tblInstanceImport AS Source
ON (Target.dbo.InstanceName= Source.dbo.InstanceName)
WHEN MATCHED THEN
UPDATE
SET [Home] = Source.dbo.[Home]
,[Version] = Source.dbo.[Version]
,[DateCollected] = GetDate();
WHEN NOT MATCHED BY TARGET THEN
UPDATE
SET tblInstanceNew.dbo.[ServerName] = Source.dbo.[Nodename]
,tblInstanceNew.dbo.[InstanceName] = Source.dbo.[WAS_Instance]
,tblInstanceNew.dbo.[DateCollected] = GetDate();
Edit: I just realized it was missing in quite a few places, so I've tried adding them all. My apologies for not being thorough in the first go 🙂
Kind regards
Lars Søe Mikkelsen
August 5, 2011 at 3:27 am
Hello,
This failed to work. Any other suggestions?
MERGE tblInstances AS TARGET
USING tblInstanceImport AS Source
ON (Target.InstanceName= Source.InstanceName)
WHEN MATCHED THEN
UPDATE
SET [Home] = Source.[Home]
,[Version] = Source.[Version]
,[DateCollected] = GetDate()
WHEN NOT MATCHED BY TARGET THEN
UPDATE
SET tblInstanceNew.dbo.[ServerName] = Source.dbo.[Nodename]
,tblInstanceNew.dbo.[InstanceName] = Source.dbo.[WAS_Instance]
,tblInstanceNew.dbo.[DateCollected] = GetDate();
August 5, 2011 at 3:29 am
tomeaton12 (8/5/2011)
Hello,This failed to work. Any other suggestions?
Yeah, see the edit on my previous post.
Should be okay now, provided that the schema name is indeed dbo.
Kind regards
Lars Søe Mikkelsen
August 5, 2011 at 3:46 am
Hello,
My schema is .dbo. Your solution below did not work. Any other suggestions?
MERGE tblInstances AS TARGET
USING tblInstanceImport AS Source
ON (Target.InstanceName= Source.InstanceName)
WHEN MATCHED THEN
UPDATE
SET tblInstances.dbo.[Home] = Source.dbo.[Home]
,tblInstances.dbo.[Version] = Source.dbo.[Version]
,tblInstances.dbo.[DateCollected] = GetDate()
WHEN NOT MATCHED BY TARGET THEN
UPDATE
SET tblInstanceNew.dbo.[ServerName] = Source.dbo.[Nodename]
,tblInstanceNew.dbo.[InstanceName] = Source.dbo.[WAS_Instance]
,tblInstanceNew.dbo.[DateCollected] = GetDate();
EDIT: Previously all the code worked except for the last UPDATE. Using the schema (dbo) this has errored all of my code. I need a way of updating a third table (tblInstanceNew)?
August 5, 2011 at 4:10 am
Hi again
Are you sure you corrected all the places?
Your post says: ON (Target.InstanceName= Source.InstanceName)
where is should be: ON (Target.dbo.InstanceName= Source.dbo.InstanceName)
Kind regards
Lars Søe Mikkelsen
August 5, 2011 at 4:17 am
Hello,
OK I have added the schema in bold still no luck. This is just making all my code error. when before it was just the last update statement.
MERGE tblInstances AS TARGET
USING tblInstanceImport AS Source
ON (Target.dbo.InstanceName= Source.dbo.InstanceName)
WHEN MATCHED THEN
UPDATE
SET Target.dbo.[Home] = Source.dbo.[Home]
,Target.dbo.[Version] = Source.dbo.[Version]
,Target.dbo.[DateCollected] = GetDate()
WHEN NOT MATCHED BY TARGET THEN
UPDATE
SET tblInstanceNew.dbo.[ServerName] = Source.dbo.[Nodename]
,tblInstanceNew.dbo.[InstanceName] = Source.dbo.[WAS_Instance]
,tblInstanceNew.dbo.[DateCollected] = GetDate();
August 5, 2011 at 4:46 am
Hi,
You can't do what you're trying.
According to BOL (http://technet.microsoft.com/en-us/library/bb510625.aspx) WHEN NOT MATCHED only allows INSERT.
And it you can't update a different table to the target.
BTW 3 part naming is <schema>.<table>.<column>
Cheers
Leyton
August 5, 2011 at 5:00 am
.
August 5, 2011 at 5:01 am
Hi,
So you cannot INSERT into a different table to the target?
EDIT: Does anyone know why this code still is not working?
There is no actuall error on the code but when I ALTER the stored procedure it errors with this message:
Msg 10739, Level 15, State 1, Procedure up_AmendInstanceImport_rs, Line 44
The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead.
MERGE dbo.tblInstances AS TARGET
USING dbo.tblInstanceImport AS Source
ON (Target.InstanceName = Source.InstanceName)
WHEN MATCHED THEN
UPDATE
SET Target.[Home] = Source.[Home]
,Target.[Version] = Source.[Version]
,Target.[DateCollected] = GetDate()
WHEN NOT MATCHED BY TARGET THEN
INSERT(tblNewInstance.[Name]
,tblNewInstance.[InstanceName]
,tblNewInstance.[DateCollected]
)
VALUES(Source.Name
,Source.Instance
,GETDATE()
);
Thanks in advance.
August 5, 2011 at 6:12 am
Hi,
Did you read the BOL article on merge that I posted earlier? There are some good examples there.
The pertinent bit though is:
Performs insert, update, or delete operations on a target table...
In other words you can't update/insert to a different table.
Also don't rely on intellisense to tell you your code is correct, it's easily fooled. Instead you should parse your code using the Blue Tick on the toolbar in SSMS (or CTRL+F5). Which would have given you the same error as you had when trying to put this into a stored procedure.
Cheers
Leyton
August 5, 2011 at 11:53 am
By virtue of using the MERGE statement you have already specified the TARGET table, so no need to quality the INSERT, UPDATE or DELETE:
INSERT ([Name]
,[InstanceName]
,[DateCollected]
)
VALUES (Source.Name
,Source.Instance
,GETDATE()
);
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply