CODEWITHSUNDEEP
sqlt-sql
Greg
Greg

Reputation: 648

SQL Query: insert if not already exists from 2 tables

I am trying to insert rows if they don't exist (for specific values) in this table.

My table where I insert is insertTable:

date (date)
created (datetime)
category (varchar)
companyId (int)
price (decimal 6,3)

I select my rows from an inner join between two tables doing :

declare @currentDateTime datetime = getDate()
declare @currentDate date = getDate()
INSERT INTO insertTable (date, created, category, companyId, price)
SELECT @currentDate, @currentDateTime, '30 Day', company.companyId, product.price
FROM product 
INNER JOIN company
ON product.companyid = company.companyid
WHERE product.price >= 0.31 AND ... other conditions on company fields ...
AND NOT EXISTS(
        SELECT  * FROM insertTable WHERE insertTable.price  = product.price
                    AND insertTable.date = @currentDateTime 
                    AND insertTable.companyid = product.companyid
                    AND LTRIM(RTRIM(insertTable.category)) = '30 Day'
    )

What am I doing wrong please?

Thanks

--edited after Gordon Linoff Comment. I insert the result of that select in my insertTable. My issue is that I get duplicates in insertTable.

If insertTable had

2014-09-26 | 2014-09-26 02:25:00 | 30 Day | 32650 | 0.600

My select will return something like

2014-09-26 | 2014-09-26 02:36:00 | 30 Day | 32650 | 0.600

However I already have that companyID and price in the insert table

Upvotes: 0

Views: 206

Answers (4)

radar
radar

Reputation: 13425

you need a correlated subquery, not a join in the subquery.

It is even better to use MERGE statement http://msdn.microsoft.com/en-us/library/bb510625.aspx

declare @currentDateTime datetime = getDate()
declare @currentDate date = getDate()
INSERT INTO insertTable (date, created, category, companyId, price)
SELECT @currentDate, @currentDateTime, '30 Day', company.companyId, product.price
FROM product 
INNER JOIN company
ON product.companyid = company.companyid
WHERE product.price >= 0.31 AND ... other conditions on company fields ...
AND NOT EXISTS(
        SELECT  1 FROM insertTable WHERE insertTable.price  = product.price
                    AND insertTable.date = @currentDate
                    AND insertTable.companyid = company.companyid
                    AND LTRIM(RTRIM(insertTable.category)) = '30 Day'
    )

EDIT:

You can do the same with MERGE like below

MERGE insertTable as target
USING ( SELECT @currentDate, 
           @currentDateTime,
               '30 Day', 
               company.companyId, 
               product.price
        FROM INNER JOIN company
    ON product.companyid = company.companyid
    WHERE product.price >= 0.31 AND ... other conditions on company fields ...
 ) as source ( currentDate, currentDateTime, category, companyid, price)
ON target.price = source.price
AND target.date = source.currentDate
and target.companyid = source.companyid
and LTRIM(RTRIM(target.category)) = '30 Day'
WHEN NOT MATCHED THEN
    INSERT (date, created, category, companyId, price)
    VALUES (source.currentDate, source.currentDatetime, source.category, source.companyId, source.price)

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

You problem statement cannot be accurate
rate is not defined

you are missing created
and you are comparing date to @currentDateTime

SELECT @currentDate, @currentDateTime, '30 Day'
     , company.companyId, product.price
  FROM product 
  JOIN company
    ON product.companyid = company.companyid
   and product.price >= 0.31 AND ... other conditions on company fields ...
AND NOT EXISTS(
               SELECT  * 
                 FROM insertTable 
                WHERE insertTable.date = @currentDate 
                --AND insertTable.created = @currentDateTime
                  AND insertTable.price  = product.price
                  AND insertTable.companyid = product.companyid
                  AND LTRIM(RTRIM(insertTable.category)) = '30 Day'
               )

Upvotes: 1

Rimas
Rimas

Reputation: 6024

I think you must modify your subquery in NOT EXISTS: @currentDateTime change to @currentDate and rate.companyid change to company.companyid (because when insert insertTable.date gets value of @currentDate and insertTable.companyid gets value of company.companyid):

...
AND NOT EXISTS(
        SELECT  * FROM insertTable WHERE insertTable.price  = product.price
                    AND insertTable.date = @currentDate 
                    AND insertTable.companyid = company.companyid
                    AND LTRIM(RTRIM(insertTable.category)) = '30 Day'
    )

Upvotes: 1

Ash8087
Ash8087

Reputation: 701

I don't think you can use the EXISTS function this way. I think you probably need to do a LEFT JOIN to the insertTable table and then add a "iT.Price IS NULL" to your WHERE clause. (This assumes Price is never actually NULL on your insertTable.)

Upvotes: 0

Related Questions