MERGE Statement assistance
-
Hi all,
I am working on an update routine, whereby I have 2 SQL tables that are in different Databases, but need to updated if different data exists, and added if non-exist.
quite a simple task .... but am struggling.
MY Script is .
MERGE dbo.AdmFormData AS Target
USING dbo.AdmFormData AS Source
ON ( Target.FormType = Source.FormType and Target.KeyField = Source.KeyField and Target.FieldName = Source.FieldName )
WHEN MATCHED and Target.AlphaValue <> Source.AlphaValue OR Target.NumericValue <> Source.NumericValue
THEN UPDATESET Target.AlphaValue = Source.AlphaValue, Target.NumericValue = Source.NumericValue
WHEN NOT MATCHED BY TARGET THEN
INSERT (padm.FormType, padm.KeyField, padm.FieldName, padm.AlphaValue)
VALUES (gadm.FormType, gadm.KeyField, gadm.FieldName, gadm.AlphaValue)
OUTPUT $action, inserted.*, deleted.*
I am getting this error message when parsed;
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITPPlease don't trust me, test the solutions I give you before using them.
-
I get a different error message
The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead.
Try this instead
MERGE dbo.AdmFormData AS TargetUSING dbo.AdmFormData AS SourceON ( Target.FormType = Source.FormType and Target.KeyField = Source.KeyField and Target.FieldName = Source.FieldName )WHEN MATCHED and Target.AlphaValue <> Source.AlphaValue OR Target.NumericValue <> Source.NumericValueTHEN UPDATE SET Target.AlphaValue = Source.AlphaValue, Target.NumericValue = Source.NumericValueWHEN NOT MATCHED BY TARGET THENINSERT (FormType, KeyField, FieldName, AlphaValue)VALUES (FormType, KeyField, FieldName, AlphaValue)OUTPUT $action, inserted.*, deleted.*;____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Brilliant I think we both got there at the same time.
It works now
- thanks for your help.
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITPPlease don't trust me, test the solutions I give you before using them.
-
Okay - I have now found another little problem...
The Production Server where I want to install this is on SQL 2005 - rather than 2008 ( which my Test area is on )
What is the best way to acheive the MERGE function without using the MERGE function and SQL 2005.
Thanks in Advance
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITPPlease don't trust me, test the solutions I give you before using them.
-
Use a separate INSERT and UPDATE statement, something like this
UPDATE TargetSET Target.AlphaValue = Source.AlphaValue, Target.NumericValue = Source.NumericValueFROM TargetDB.dbo.AdmFormData AS TargetINNER JOIN SourceDB.dbo.AdmFormData AS Source ON (Target.FormType = Source.FormType and Target.KeyField = Source.KeyField and Target.FieldName = Source.FieldName )AND (Target.AlphaValue <> Source.AlphaValue OR Target.NumericValue <> Source.NumericValue)INSERT INTO TargetDB.dbo.AdmFormData(FormType, KeyField, FieldName, AlphaValue)SELECT Source.FormType, Source.KeyField, Source.FieldName, Source.AlphaValueFROM SourceDB.dbo.AdmFormData AS SourceWHERE NOT EXISTS(SELECT * FROM TargetDB.dbo.AdmFormData AS Target WHERE Target.FormType = Source.FormType and Target.KeyField = Source.KeyField and Target.FieldName = Source.FieldName)____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Brilliant - thats worked a treat -
One last question - can I incorporate the OUTPUT functionality to get the result set returned ?
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITPPlease don't trust me, test the solutions I give you before using them.
-
Both INSERT and UPDATE support the OUTPUT clause
UPDATE TargetSET Target.AlphaValue = Source.AlphaValue, Target.NumericValue = Source.NumericValueOUTPUT inserted.*, deleted.*FROM TargetDB.dbo.AdmFormData AS TargetINNER JOIN SourceDB.dbo.AdmFormData AS Source ON (Target.FormType = Source.FormType and Target.KeyField = Source.KeyField and Target.FieldName = Source.FieldName )AND (Target.AlphaValue <> Source.AlphaValue OR Target.NumericValue <> Source.NumericValue)INSERT INTO TargetDB.dbo.AdmFormData(FormType, KeyField, FieldName, AlphaValue)OUTPUT inserted.*SELECT Source.FormType, Source.KeyField, Source.FieldName, Source.AlphaValueFROM SourceDB.dbo.AdmFormData AS SourceWHERE NOT EXISTS(SELECT * FROM TargetDB.dbo.AdmFormData AS Target WHERE Target.FormType = Source.FormType and Target.KeyField = Source.KeyField and Target.FieldName = Source.FieldName)____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Thanks again - I keep looking past the obvious and think its more complicated that it should be.
I have now created a stored procedures for this and using the output clause to create an audit table.
Thanks for your help
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITPPlease don't trust me, test the solutions I give you before using them.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply