title: SQL Server How to insert when not exist?
body: <p>I have two tables, one is called Invoices and another is called Records.</p>
  
  <pre><code>CREATE TABLE Invoices
  (
      InvoiceNum INT NOT NULL,
      Amount DECIMAL,
      RecordPK UNIQUEIDENTIFIER NOT NULL
  )
  
  CREATE TABLE Records(
      RecordPK UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
      StartNum INT NOT NULL,
      NextNum INT NOT NULL,
      MaxNum INT NOT NULL,
      InvPrefix VARCHAR(2) NOT NULL
  )
  </code></pre>
  
  <p>The records table will record the invoice start number, how many invoices we have created(NextNum) and how many invoices we can create(MaxNum).</p>
  
  <p>For example, Assume we have several records in two tables.</p>
  
  <p>Invoice Table:</p>
  
  <pre><code>InvoiceNum    Amount    RecordPk
  1             19.00     EDFA0541-5583-4CDD-BDFF-21D6F6504522
  2             50.00     EDFA0541-5583-4CDD-BDFF-21D6F6504522
  3             3.00      EDFA0541-5583-4CDD-BDFF-21D6F6504522
  10            1.00      D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
  11            99.00     D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
  12            13.00     D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
  </code></pre>
  
  <p>Records Table:</p>
  
  <pre><code>RecordPk                                StartNum    NextNum    MaxNum    Prefix
  EDFA0541-5583-4CDD-BDFF-21D6F6504522    1           4          10        AA
  D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9    10          13         14        AA
  </code></pre>
  
  <p>My question is when I search the invoice table with Prefix AA, how can I get the result like below, the InvoiceNum should reach the MaxNum, the Amount and RecordPK of not exist rows should left blank, the Remark column should fill with Blank.</p>
  
  <pre><code>InvoiceNum    Amount    RecordPk                                Remark
  1             19.00     EDFA0541-5583-4CDD-BDFF-21D6F6504522
  2             50.00     EDFA0541-5583-4CDD-BDFF-21D6F6504522
  3             3.00      EDFA0541-5583-4CDD-BDFF-21D6F6504522
  4                                                               Blank
  5                                                               Blank
  6                                                               Blank
  7                                                               Blank
  8                                                               Blank
  9                                                               Blank
  10            1.00      D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
  11            99.00     D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
  12            13.00     D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
  13                                                              Blank
  14                                                              Blank
  </code></pre>