SQL Join Update Multiple Tables (if field exists)
This is my first post on StackOverflow. I've been using this amazing resource for several years to answer hundreds of SQL and PowerShell questions, but I hold back for a few seconds.
I am using SQL Server 2014 SP2 and I am trying to upgrade to DATABASE1, FIELD1, then FIELD2, then FIELD3 from several other databases.
FIELD1 can exist in one of several other databases. FIELD1 may not exist in all databases where the problem is.
I have the following (anonymous) request and it works:
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 say FIELD8, as it exists in DATABASE1 but not DATABASE2, DATABASE3, or DATABASE4. Then I get the following error:
Msg 207, Level 16, State 1, Line 30
Invalid column name 'FIELD8'.
From my Google and StackOverflow searches, I tried using (for the first time) a:
IF EXISTS (SELECT COLUMN1 FROM Database2.Table2 WHERE Column1='Field8')
EXEC .......
But where did I start to really struggle.
Hope the above makes sense. Any advice or help would be greatly appreciated.
NB I have about 3000 fields in Database1 that need updating. I've built all my UPDATE statements this far dynamically.
source to share
First, sp_MSforeachdb
it is not reliable. For a working alternative, check here: Making sp_MSforeachdb more robust and flexible - Aaron Bertrand
Second, you can use system views to check if a column exists in a given table using the sys.columns
following:
if exists (
select 1
from sys.columns c
where c.name = 'pilots_id' /* column name */
and c.object_id = object_id(N'pilots') /* table name */
)
begin
select 'Pilots_Id exists' /* do stuff */
end
registry: http://rextester.com/UUXCB18567
source to share
You can create a stored proc that will look for columns and tables in system tables:
ALTER PROCEDURE dbo.check_table_exists
-- Add the parameters for the stored procedure here
@table_name nvarchar(255),
@column_name nvarchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @SQLString nvarchar(max),
@ParmDefinition nvarchar(500) = N'@table_name nvarchar(255), @column_name nvarchar(255)';
IF OBJECT_ID(N'tempdb..#check_column_exists') is not null DROP TABLE #check_column_exists
CREATE TABLE #check_column_exists (
db nvarchar(500) NULL,
column_exists bit NULL
)
SELECT @SQLString =
(
SELECT N'USE '+QUOTENAME([name]) +'; '+
'INSERT INTO #check_column_exists '+
'SELECT '''+[name]+''' as [db], '+
' COUNT(*) as column_exists ' +
'FROM sys.tables t ' +
'INNER JOIN sys.columns c ' +
' ON t.[object_id] = c.[object_id] ' +
'WHERE t.[name] = @table_name and c.[name] = @column_name; '
FROM sys.databases
WHERE [name] NOT IN (
'msdb',
'model',
'tempdb',
'master'
)
FOR XML PATH('')
) + 'SELECT [db] FROM #check_column_exists WHERE column_exists = 1; DROP TABLE #check_column_exists;'
EXEC sp_executesql @SQLString, @ParmDefinition, @table_name = @table_name, @column_name = @column_name
END
GO
You can change it to search for columns only and output the name of the database and table, or whatever.
Output:
db
-----------
DATABASE1
DATABASE4
...
etc
After that, you can write this to a table and use it to dynamically update SQL:
DECLARE @table_name nvarchar(255) = 'SomeTable',
@column_name nvarchar(255) = 'SomeField'
DECLARE @results TABLE (
db nvarchar(500)
)
INSERT INTO @results
EXEC dbo.check_table_exists @table_name, @column_name
--...Here goes building of dynamic SQL query to update data
source to share