SQL Server How to insert when not exist?

Question

I have two tables, one is called Invoices and another is called Records.

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
)

The records table will record the invoice start number, how many invoices we have created(NextNum) and how many invoices we can create(MaxNum).

For example, Assume we have several records in two tables.

Invoice Table:

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

Records Table:

RecordPk                                StartNum    NextNum    MaxNum    Prefix
EDFA0541-5583-4CDD-BDFF-21D6F6504522    1           4          10        AA
D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9    10          13         14        AA

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.

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

Answer 1

I will do it this way:

    IF OBJECT_ID('tempdb..#Invoices') IS NOT NULL DROP TABLE #Invoices

    CREATE TABLE #Invoices
    (
        InvoiceNum INT NOT NULL,
        Amount DECIMAL,
        RecordPK UNIQUEIDENTIFIER NOT NULL
    )

    IF OBJECT_ID('tempdb..#Records') IS NOT NULL DROP TABLE #Records

    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
    )

    INSERT INTO #Invoices
    SELECT 1, 19.00, 'EDFA0541-5583-4CDD-BDFF-21D6F6504522'
    UNION SELECT 2 , 50.00, 'EDFA0541-5583-4CDD-BDFF-21D6F6504522'
    UNION SELECT 3 , 3.00 , 'EDFA0541-5583-4CDD-BDFF-21D6F6504522'
    UNION SELECT 10 , 1.00 , 'D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9'
    UNION SELECT 11 , 99.00, 'D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9'
    UNION SELECT 12 , 13.00, 'D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9'

    INSERT INTO #Records
    SELECT 'EDFA0541-5583-4CDD-BDFF-21D6F6504522', 1, 4, 10, 'AA'
    UNION SELECT 'D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9', 10, 13, 14, 'AA'

    DECLARE @MAX_NUM INT = (SELECT MAX(MaxNum) FROM #Records)
    DECLARE @TEMP_INV TABLE (InvoiceNum INT)
    INSERT INTO @TEMP_INV
    SELECT Num
    FROM
    (
        SELECT ROW_NUMBER() OVER(ORDER BY object_id) AS Num FROM sys.objects
    ) A
    WHERE Num <= @MAX_NUM

    IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP

    SELECT I.InvoiceNum, I.Amount, I.RecordPK
    INTO #TEMP
    FROM #Invoices I
    INNER JOIN #Records R
        ON I.RecordPK = R.RecordPK
    WHERE R.InvPrefix = 'AA'

    SELECT A.InvoiceNum, B.Amount, B.RecordPK, CASE WHEN B.InvoiceNum IS NULL THEN 'BLANK' END AS Remark
    FROM @TEMP_INV A
    LEFT JOIN #TEMP B
        ON A.InvoiceNum = B.InvoiceNum

Answer 2

You need a LEFT JOIN

SELECT I.*,
       CASE WHEN I.InvoiceNum IS NULL THEN 'Blank' END Remark
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14)) RC (InvoiceNum)
LEFT JOIN Invoices I
     ON RC.InvoiceNum = I.InvoiceNum;

The value 1 is the StartNum and 14 is the MAX MaxNum. I used VALUES cause the number is know, you can use a RecursiveCTE to generate the missing InvoiceNum then LEFT JOIN the CTE with your table.

Demo

Answer 3

@Andrey Nikolov has it covered, however I've been working on this for the last 15 minutes so I thought I'd post it anyway.

Essentially an intermediary table should be used to count up the values you don't have, then in my version of this answer I've used a union query to generate the "Blank" value. I have not included the unique identifier for brevity but the application is the same.

if OBJECT_ID('tempdb..#invoice') is not null drop table #invoice;
if OBJECT_ID('tempdb..#rowcount') is not null drop table #rowcount;

create table #invoice
    (
        invoicenum int,
        amount decimal
    );


insert into #invoice (invoicenum, amount)
values
(1, 19.00),
(2, 50.00),
(3, 3.00),
(10, 1.00),
(11, 99.00),
(12, 13.00);


create table #rowcount

    (
        rownumber int 
    );

declare @max int = 1;

select @max=count(*) from #invoice;

declare @runs int = 1;

while @runs<=@max
begin
insert into #rowcount (rownumber)
values (@runs);
select @runs=@runs+1;
end


select invoicenum, cast(amount as nvarchar(25)) as amount from #invoice
union
select rownumber, 'BLANK' from #rowcount r left join #invoice i on 
r.rownumber=i.invoicenum where i.invoicenum is null
order by invoicenum;

drop table #invoice, #rowcount;

Answer 4

You need to generate a table with numbers to cover the range of numbers that you need (for each row in Records table, from StartNum to MaxNum). You can do this for example, by selecting from some existing table with enough rows and using ROW_NUMBER window function. Then filter this sequence to include only the numbers you need. Left join the Invoices table to show the data for the corresponding invoice and use IIF function to check is there invoice with this number or not.

declare @Invoices table(InvoiceNum INT NOT NULL, Amount DECIMAL, RecordPK UNIQUEIDENTIFIER NOT NULL)
declare @Records table(RecordPK UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, StartNum INT NOT NULL, NextNum INT NOT NULL, MaxNum INT NOT NULL, InvPrefix VARCHAR(2) NOT NULL)

insert into @Invoices(InvoiceNum, Amount, RecordPk) values
(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')

insert into @Records(RecordPk, StartNum, NextNum, MaxNum, InvPrefix) values
('EDFA0541-5583-4CDD-BDFF-21D6F6504522',    1 ,          4 ,         10,        'AA'),
('D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9',    10,          13,         14,        'AA')

;with numbers as (select ROW_NUMBER() over(order by object_id) as No from sys.objects)
select
    n.No as InvoiceNum
    , inv.Amount
    , inv.RecordPK
    , IIF(inv.InvoiceNum is null, 'Blank', null) as Remark
from numbers n
left join @Invoices inv on n.No = inv.InvoiceNum
where exists(select * from @Records r where r.StartNum <= n.No and n.No <= r.MaxNum)