This is my first post to StackOverflow. I've been using this amazing resource for a number of years to answer hundreds of SQL and PowerShell questions, however this one has me stumped for a number of days.
I am using SQL Server 2014 SP2 and I am trying to do an update to DATABASE1, FIELD1, then FIELD2 then FIELD3 from multiple other database.
FIELD1 may exist in one of multiple other databases. FIELD1 may not exist in ALL databases - which is where I have the problem.
I have the following (anonymised) query and it appears to be working:
EXEC sp_MSforeachdb 'IF ''?'' IN (''DATABASE2'',''DATABASE3'',''DATABASE4'')
BEGIN
UPDATE DATABASE1.PARAMETER
SET B.[VALUE] = A.[FIELD1]
FROM DATABASE1.TABLE1 B
INNER JOIN ?.dbo.[TABLE2] A
ON A.JOINVALUE = B.JOINVALUE
WHERE B.COLUMN2 = ''SOMETHING''
AND COLUMN3= ''PF.T.FIELD1''
END ;'
Until I get to say FIELD8, as it exists in DATABASE1 but not in DATABASE2, DATABASE3 or DATABASE4. I then get the following error:
Msg 207, Level 16, State 1, Line 30
Invalid column name 'FIELD8'.
From my Google and StackOverflow searches, I've tried to use (for the first time) a:
IF EXISTS (SELECT COLUMN1 FROM Database2.Table2 WHERE Column1='Field8')
EXEC .......
But that's where I started to really struggle.
Hope the above makes sense. Any tips or assistance would be greatly appreciated.
N.B. I have about 3,000 fields in Database1 which require updating. I've so-far built all my UPDATE statements dynamically.