sql-server - SQL 更新连接多个表(如果字段存在)

Question

这是我在 StackOverflow 上的第一篇文章。多年来，我一直在使用这个惊人的资源来回答数百个 SQL 和 PowerShell 问题，但是这个问题让我困惑了很多天。

我正在使用 SQL Server 2014 SP2，我正在尝试从多个其他数据库更新 DATABASE1、FIELD1，然后是 FIELD2，然后是 FIELD3。

FIELD1 可能存在于多个其他数据库之一中。FIELD1 可能不存在于所有数据库中 - 这就是我遇到问题的地方。

Database Design Link

我有以下(匿名的)查询，它似乎有效:

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 ;'

直到我开始说 FIELD8，因为它存在于 DATABASE1 中，但不存在于 DATABASE2、DATABASE3 或 DATABASE4 中。然后我收到以下错误:

Msg 207, Level 16, State 1, Line 30     
Invalid column name 'FIELD8'.

通过我的 Google 和 StackOverflow 搜索，我尝试(第一次)使用:

IF EXISTS (SELECT COLUMN1 FROM Database2.Table2 WHERE Column1='Field8')
  EXEC .......

但这正是我开始真正挣扎的地方。

希望以上是有道理的。任何提示或帮助将不胜感激。

注意我在 Database1 中有大约 3,000 个字段需要更新。到目前为止，我已经动态构建了所有更新语句。

Answer 1

您可以创建存储过程，它将在系统表中搜索列和表:

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

您可以将其更改为仅搜索列并输出数据库和表名或其他任何内容。

输出是:

db
-----------
DATABASE1
DATABASE4
...
etc

之后您可以将其写入表并用于动态 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