sql - 使用 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

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中得到了结果，但是您可以将结果导出到文件或文本，然后在想要执行时将其复制粘贴到查询窗口中。