Generate Insert Scripts with IF NOT EXISTS

Question

I have a master table in a database.

Example Menu table

+-----------+-----------+-------------+---------+------------------------+
| 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 |
+-----------+-----------+------+------+---------+------------------------+

I used to generate master data script like below for each record.

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

-- Repeating same manual record creation work for all 70 records & other master data(10k rows)

However there is some existing table ApplicationMenu in another database is having same column, datatypes. We would like to generate the below script automatically for us by using some stored procedure.

Is it possible to create a procedure like below

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

 -- In some loop create those above insert statements
END

We would like to execute like below

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

Here the {Id} & {Key} will be read from every row from existing table and replaced.

This will actually reduce lot of manual work for us.

Note:

We could not use SQL server insert script generation tool, since we want to check the data existence as well as need to keep the records added by user using our application.

Need to generate a insert scripts so that we can just run in future, even when ApplicationTable is not available

Is it possible to write such a procedure to generate insert script from other table based on existence? Like how sql server Generate Scripts work for table creation by looking into INFORMATION_SCHEMA table, same way I am expecting for this.

Final output of the procedure would be like PRINT @insert_Sql_Statements

Answer 1

Your Data

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

Query to Create Script

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

Generated Script

╔════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                                                                                                                                                                                                          (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     ║
╚════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

Note

I have got results back in Grid but you can export the results to a file or to text and copy paste it into your query window when you want to execute it.