生成包含IF NOT EXISTS的插入脚本

Question

我可以帮您翻译成中文，以下是翻译的结果：

我在数据库中有一个主表。

示例菜单表

+-----------+-----------+-------------+---------+------------------------+
| Id        | Key       | Display Text| ParentId| CreatedOn
+-----------+-----------+-------------+---------+------------------------+
| 1         | Home      | Home        | NULL    |2014-01-14 21:17:37.387 |
| 2         | About     | About Us    | NULL    |2014-01-14 21:17:37.387 |
| 3         | Contact   | Contact Us  | NULL    |2014-01-14 21:17:37.387 |
+-----------+-----------+------+------+---------+------------------------+

我曾为每个记录生成以下类似的主数据脚本。

IF NOT EXISTS(SELECT 1 FROM [Menu] WHERE Id=1 AND Key='Home')
BEGIN
SET IDENTITY_INSERT [dbo].[Menu] ON

INSERT INTO [dbo].[Menu]
           (Id
           ,[Key]
           ,[DisplayText]
           ,[ParentId]
           ,[CreatedOn])
     VALUES
           (1
           ,'Home'
           ,'Home'
           ,NULL
           ,GETDATE()
           )
SET IDENTITY_INSERT [dbo].[Menu] OFF

END
GO

-- 重复手动创建70条记录和其他主数据的工作（10k行）

但是，另一个数据库中存在一些现有的表ApplicationMenu具有相同的列，数据类型。我们希望通过使用某些存储过程自动生成以下脚本。

是否可以创建如下存储过程？

CREATE PROCEDURE spGenerateInsertScripts
(
  @SourceTableName VARCHAR(100),
  @ExistsWhereClauseTemplate NVARCHAR(1000),
  @TargetTableName VARCHAR(100)
)
BEGIN

 -- In some loop create those above insert statements
END

我们希望执行以下操作。

  exec spGenerateInsertScripts 'ApplicationMenu'
                               , 'WHERE Id={Id} AND Key={Key}'
                               , 'Menu'

在此，将从现有表中的每一行读取{Id} & {Key} 并进行替换。

这实际上会减少我们很多手动工作。

注意:

我们无法使用 SQL Server 插入脚本生成工具，因为我们想要检查数据是否存在，并且需要保留由用户使用我们的应用程序添加的记录。

需要生成插入脚本，以便将来可以直接运行，即使 ApplicationTable 不可用也可以运行。

是否可能编写这样的过程，以根据现有情况从其他表生成插入脚本？就像 SQL Server 的 Generate Scripts 通过查看 INFORMATION_SCHEMA 表创建表，期望使用相同的方法。

最终过程的输出结果将如PRINT @insert_Sql_Statements

- Billa

---

所以你想使用ApplicationMenu表（在其他数据库中）来创建菜单数据库中的行（如果这些行不存在）。使用ApplicationMenu的Id和Key列？ - Umair

为什么不创建一个接受表值参数的存储过程，将值传递给存储过程，并返回添加到目标表中的值呢？ - M.Ali

@Umair，是的。这两个表具有相同的列名和数据类型。如果不存在，则我想将所有ApplicationMenu导入到我的菜单表中。 - Billa

@M.Ali，我需要一个插入脚本，这样我可以随时运行，即使ApplicationMenu表不可用。 - Billa

Answer 1

您的数据

DECLARE @Table TABLE(Id INT, [Key] VARCHAR(30),[Display Text] VARCHAR(30), ParentId INT,  CreatedOn DATETIME)
INSERT INTO @Table VALUES 
(1,'Home'   ,'Home'      ,NULL, '2014-01-14 21:17:37.387'), 
(2,'About'  ,'About Us'  ,NULL, '2014-01-14 21:17:37.387'),
(3,'Contact','Contact Us',NULL, '2014-01-14 21:17:37.387')

创建脚本的查询

SELECT  N'IF NOT EXISTS(SELECT 1 FROM [Menu] WHERE Id='+  CAST(Id AS NVARCHAR(10)) 
       + ' AND Key='''+ CAST([Key] AS NVARCHAR(1000)) +''')' + CHAR(10) 
        + N'BEGIN ' + CHAR(10) + '
        SET IDENTITY_INSERT [dbo].[Menu] ON ' + CHAR(10) + ' 

        INSERT INTO [dbo].[Menu]      ' + CHAR(10) + ' 
                   (Id    ' + CHAR(10) + ' 
                   ,[Key] ' + CHAR(10) + ' 
                   ,[DisplayText]' + CHAR(10) + ' 
                   ,[ParentId]' + CHAR(10) + ' 
                   ,[CreatedOn])' + CHAR(10) + ' 
             VALUES' + CHAR(10) + ' 
                   ( '  + ISNULL(CAST(Id AS NVARCHAR(10)), 'NULL') + ' ' + CHAR(10) + ' 
                   ,''' + ISNULL(CAST([Key] AS NVARCHAR(1000)), 'NULL') +''' ' + CHAR(10) + ' 
                   ,''' + ISNULL(CAST([Display Text] AS NVARCHAR(1000)), 'NULL')  + ''' ' + CHAR(10) + ' 
                   ,' +  ISNULL(CAST(ParentId AS NVARCHAR(10)), 'NULL')  + ' ' + CHAR(10) + ' 
                   ,GETDATE() ' + CHAR(10) + ' 
                   ) ' + CHAR(10) + ' 
        SET IDENTITY_INSERT [dbo].[Menu] OFF ' + CHAR(10) + ' 
        END ' + CHAR(10) + ' 
        GO ' + CHAR(10) + ' '+ CHAR(10)
FROM @Table

生成的脚本

╔════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                                                                                                                                                                                                          (No column name)                                                                                                                                                                                                                          ║
╠════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║ IF NOT EXISTS(SELECT 1 FROM [Menu] WHERE Id=1 AND Key='Home') BEGIN      SET IDENTITY_INSERT [dbo].[Menu] ON         INSERT INTO [dbo].[Menu]                 (Id               ,[Key]            ,[DisplayText]           ,[ParentId]           ,[CreatedOn])        VALUES           ( 1            ,'Home'            ,'Home'            ,NULL            ,GETDATE()            )       SET IDENTITY_INSERT [dbo].[Menu] OFF       END       GO                 ║
║ IF NOT EXISTS(SELECT 1 FROM [Menu] WHERE Id=2 AND Key='About') BEGIN      SET IDENTITY_INSERT [dbo].[Menu] ON         INSERT INTO [dbo].[Menu]                 (Id               ,[Key]            ,[DisplayText]           ,[ParentId]           ,[CreatedOn])        VALUES           ( 2            ,'About'            ,'About Us'            ,NULL            ,GETDATE()            )       SET IDENTITY_INSERT [dbo].[Menu] OFF       END       GO           ║
║ IF NOT EXISTS(SELECT 1 FROM [Menu] WHERE Id=3 AND Key='Contact') BEGIN      SET IDENTITY_INSERT [dbo].[Menu] ON         INSERT INTO [dbo].[Menu]                 (Id               ,[Key]            ,[DisplayText]           ,[ParentId]           ,[CreatedOn])        VALUES           ( 3            ,'Contact'            ,'Contact Us'            ,NULL            ,GETDATE()            )       SET IDENTITY_INSERT [dbo].[Menu] OFF       END       GO     ║
╚════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

注意

我已经从 Grid 得到了结果，但是你可以将结果导出到文件或文本，并在需要执行时复制粘贴到查询窗口中。

- M.Ali

---

哎呀，慢了24分钟，基本上得到了相同的答案。向您致敬，因为您先到达了那里。 - Todd Bowles

Answer 2

假设我正确理解了你的问题，你提出的方法（将where子句作为参数）听起来并不好，并且可能会导致许多其他问题（例如SQL注入，验证SQL字符串格式是否正确等）。考虑使用以下方法，该方法使用链接服务器。

SET IDENTITY_INSERT [dbo].[Menu] ON
GO

INSERT INTO [dbo].[Menu] ([Id],[Key],[DisplayText],[ParentId],[CreatedOn])
SELECT a.Id, a.Key, a.Key, NULL, GETDATE()
FROM [ApplicationMenu_Instance].[ApplicationMenu_Database].[dbo].[ApplicationMenu] AS a
WHERE NOT EXISTS (
    SELECT 1
    FROM [dbo].[Menu] AS m
    WHERE m.Id = a.Id
        AND m.Key = a.Key
)

SET IDENTITY_INSERT [dbo].[Menu] OFF
GO

更新：既然您想要返回插入脚本，那么动态SQL如何呢：

CREATE PROCEDURE spGenerateInsertScripts
(
    @SourceTable VARCHAR(100),
    @TargetTable VARCHAR(100)
)  
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = '
    SET IDENTITY_INSERT [dbo].[Menu] ON
    GO

    INSERT INTO [dbo].[' + @TargetTable + '] ([Id],[Key],[DisplayText],[ParentId],[CreatedOn])
    SELECT a.Id, a.Key, a.Key, NULL, GETDATE()
    FROM ' + @SourceTable + '  AS a
    WHERE NOT EXISTS (
        SELECT 1
        FROM [dbo].[' + @TargetTable + '] AS m
        WHERE m.Id = a.Id
            AND m.Key = a.Key
    )

    SET IDENTITY_INSERT [dbo].[Menu] OFF
    GO
   ';

    SELECT @SQL;
END

- Umair

---

但是我需要它作为“文本脚本”，以便我可以随时运行它，即使应用程序菜单不可用。我想在ApplicationMenu可用时生成脚本文本，之后可以随时重新运行。 - Billa

抱歉，我不太明白。为什么你不能只是使用上面的代码创建一个存储过程，然后在需要时执行它呢？ - Umair

你想要这样做，以便可以通过传递参数来决定源应用程序菜单表的位置吗？ - Umair

我正在寻找类似于使用SQL Server Generate Scripts工具生成存储过程脚本的方法。在此过程中，您可以选择创建一个表，当数据库中不存在该表时。因此，它将通过检查“INFORMATION_SCHEMA”表自动为我们生成表脚本。同样的方式。 - Billa

我想要的是值语句，而不是 SELECT a.Id, a.Key, a.Key, NULL, GETDATE() FROM ApplicationMenu。请检查我的问题。目前我正在逐个插入。我希望有一些辅助过程可以通过读取另一个表来生成这些记录。 - Billa

让我们在聊天室里继续此讨论。 - Umair

Answer 3

您可以使用SQL语句生成所需的插入语句。然后，您只需要将输出复制并粘贴到您想要执行查询的地方即可。

它不是一个通用的解决方案来创建从另一个表中插入语句的脚本，但它会极大地减少特定情况下所需的手动工作。您可以配置目标表的名称，但列名称和值以及检索数据的表的名称都是硬编码的。

它假定输入的目标表与检索数据的表具有相同的模式。

DECLARE @TARGET_TABLE AS VARCHAR(100) = '[dbo].[Menu]'

SELECT Script
FROM
(
    SELECT Id, [Key], 0 AS [Order], 
        'IF NOT EXISTS(SELECT 1 FROM ' + @TARGET_TABLE + 
        ' WHERE Id=' + CONVERT(varchar(100), Id) + 
        ' AND Key=''' + [Key] + ''')' AS Script
    FROM ApplicationMenu
    UNION
    SELECT Id, [Key], 1 AS [Order], 'BEGIN' AS Script
    FROM ApplicationMenu
    UNION
    SELECT Id, [Key], 2, 'SET IDENTITY_INSERT ' + @TARGET_TABLE + ' ON'
    FROM ApplicationMenu
    UNION
    SELECT Id, [Key], 3, 
        'INSERT INTO ' + @TARGET_TABLE + 
        ' VALUES(' + 
        CONVERT(varchar(11), Id) + ', ''' + 
        [Key] + ''', ''' + 
        [DisplayText] + ''', ' + 
        ISNULL(CONVERT(varchar(11), ParentId), 'NULL') + 
        ', GETDATE())'
    FROM ApplicationMenu
    UNION
    SELECT Id, [Key], 4, 'SET IDENTITY_INSERT ' + @TARGET_TABLE + ' OFF'
    FROM ApplicationMenu
    UNION
    SELECT Id, [Key], 5, 'END'
    FROM ApplicationMenu
) AS ScriptInfo
ORDER BY Id, [Key], [Order]

说实话，这个脚本看起来有点痛苦，但它完成了工作。

如果你真的想要一个通用解决方案，你可能更容易在某种编程语言（比如C#）中实现它。使用C#实现的好处是你可以将库导入SQL Server并像调用存储过程一样调用它（我想，我以前从未做过这样的事情）。

此外，还有一些可以为您生成此类脚本的工具可用。如果我没记错的话，RedGate SQL Data Compare 可以相当轻松地完成这种事情。可能也会有其他类似的工具。

- Todd Bowles