title: Generate Insert Scripts with IF NOT EXISTS
body: <p>I have a master table in a database. </p>
  
  <p>Example Menu table</p>
  
  <pre><code>+-----------+-----------+-------------+---------+------------------------+
  | 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 |
  +-----------+-----------+------+------+---------+------------------------+
  </code></pre>
  
  <p>I used to generate master data script like below for each record.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>-- Repeating same manual record creation work for all 70 records &amp; other master data(<code>10k rows</code>)</p>
  
  <p>However there is some existing table <code>ApplicationMenu</code> in another database is having same column, datatypes. We would like to generate the below script automatically for us by using some stored procedure.</p>
  
  <p>Is it possible to create a procedure like below</p>
  
  <pre><code>CREATE PROCEDURE spGenerateInsertScripts
  (
    @SourceTableName VARCHAR(100),
    @ExistsWhereClauseTemplate NVARCHAR(1000),
    @TargetTableName VARCHAR(100)
  )
  BEGIN
  
   -- In some loop create those above insert statements
  END
  </code></pre>
  
  <p>We would like to execute like below</p>
  
  <pre><code>  exec spGenerateInsertScripts 'ApplicationMenu'
                                 , 'WHERE Id={Id} AND Key={Key}'
                                 , 'Menu'
  </code></pre>
  
  <p>Here the <strong>{Id} &amp; {Key}</strong> will be read from every row from existing table and replaced.</p>
  
  <p>This will actually reduce lot of manual work for us. </p>
  
  <p><em><strong>Note:</em></strong></p>
  
  <p>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.</p>
  
  <p>Need to generate a insert scripts so that we can just run in future, even when ApplicationTable is not available</p>
  
  <p>Is it possible to write such a procedure to generate insert script from other table based on existence? Like how sql server <code>Generate Scripts work for table creation by looking into INFORMATION_SCHEMA</code> table, same way I am expecting for this. </p>
  
  <p>Final output of the procedure would be like <code>PRINT @insert_Sql_Statements</code></p>