title: Insert default values if not exist in SQL Server
body: <p>I have one question about SQL Server: how to dynamically load default values for all tables if 9999 values does not exist in the tables?</p>
  
  <p>The database has three tables. I want load default 9999 values for unique column</p>
  
  <ul>
  <li>if datatype is <code>varchar</code> then pass <code>NA</code> value</li>
  <li>if datatype is <code>int</code> then pass 0 value</li>
  <li>if datatype is <code>date</code> then pass 1900-01-01 value</li>
  </ul>
  
  <p>Here sourceFiedls table is maintain all table related fields information.</p>
  
  <pre><code>CREATE TABLE [dbo].[dept]
  (
      [deptid] [INT] NULL,
      [dname] [VARCHAR](50) NULL,
      [loc] [VARCHAR](50) NULL
  )
  
  CREATE TABLE [dbo].[emp]
  (
      [eid] [INT] NULL,
      [ename] [VARCHAR](50) NULL,
      [doj] [DATE] NULL,
      [sal] [MONEY] NULL,
      [deptid] [INT] NULL
  ) 
  
  CREATE TABLE [dbo].[loc]
  (
      [locid] [INT] NULL,
      [locname] [VARCHAR](50) NULL
  ) 
  
  CREATE TABLE [dbo].[sourceFields]
  (
      [tablename] [VARCHAR](50) NULL,
      [tablecolumns] [VARCHAR](50) NULL
  ) 
  
  INSERT INTO [dbo].[dept] ([deptid], [dname], [loc]) 
  VALUES (1, N'abc', N'hy')
  GO
  
  INSERT INTO [dbo].[emp] ([eid], [ename], [doj], [sal], [deptid]) 
  VALUES (1, N'ab', CAST(N'1988-10-04' AS Date), 100.0000, 10)
  GO
  
  INSERT INTO [dbo].[loc] ([locid], [locname]) VALUES (1, N'hyd')
  GO
  
  INSERT INTO [dbo].[sourceFields] ([tablename], [tablecolumns]) 
  VALUES (N'dept', N'deptid'), (N'dept', N'dname'), (N'dept', N'loc'),
         (N'emp', N'eid'), (N'emp', N'ename'), (N'emp', N'doj'),
         (N'emp', N'sal'), (N'emp', N'deptid'), (N'loc', N'locid'), 
         (N'loc', N'locname')
  GO
  </code></pre>
  
  <p>Based on above table I want check the data if 9999 values exist or not.</p>
  
  <p>For dept table :</p>
  
  <pre><code>IF NOT EXISTS (SELECT * FROM dept WHERE  deptid=9999 )
  BEGIN
      INSERT INTO [dbo].[dept] ([deptid], [dname], [loc]) 
      VALUES (9999, 'NA', 'NA')
  END
  </code></pre>
  
  <p>For <code>Emp</code> table : </p>
  
  <pre><code>IF NOT EXISTS (SELECT * FROM emp WHERE  eid=9999 )
  BEGIN
      INSERT INTO [dbo].[emp] ([eid], [ename], [doj], [sal], [deptid]) 
      VALUES (9999, 'NA', CAST('1900-01-01' AS Date), 0, 0)
  END
  </code></pre>
  
  <p>For <code>Loc</code> table : </p>
  
  <pre><code>IF NOT EXISTS (SELECT * FROM loc WHERE  locid=9999 )
  BEGIN
      INSERT INTO [dbo].[loc] ([locid], [locname]) 
      VALUES (9999, 'NA')
  END
  </code></pre>
  
  <p>Here I have written 3 statements for 3 tables checking default values exist or not.
  Instead of these 3 statements, how to write a dynamic query like using cursor concept and table loop iterate values - if exist or not if not exist then insert.
  this table maintain tablenames and filed names to generate dynamic insert statments and values.</p>
  
  <pre><code>CREATE TABLE [dbo].[Tableinfo](
      [TableName] [varchar](50) NULL,
      [ColumnsList] [varchar](1000) NULL
  ) 
  
  INSERT [dbo].[Tableinfo] ([TableName], [ColumnsList]) VALUES (N'dept',N'deptid,dname,loc')
  INSERT [dbo].[Tableinfo] ([TableName], [ColumnsList]) VALUES (N'emp', N'eid,ename,doj,sal,deptid')
  INSERT [dbo].[Tableinfo] ([TableName], [ColumnsList]) VALUES (N'loc', N'locid,locname')
  </code></pre>
  
  <p>Can you please tell me how to write dynamic query for inserting default values into a table?</p>