SQL INSERT values if they do not already exist

Question

I want this query to insert all the distinct webshop dates in to the second table if they do not already exist for the webshop DataSets

The following query seems to ignore the AND t1.[DataSet] = 'webshop' parameter and not insert the new webshop values as they contain the same date ranges ie. they both contain 01/02/2013

INSERT INTO [ImportedDateRange] ([DataSet],[DateRange]) select DISTINCT 'webshop', cast(T2.[OrderCreatedDate] as DATE) from webshop T2 left join [ImportedDateRange] T1 on cast(T2.[OrderCreatedDate] as DATE) = t1.[DateRange] where t1.[DateRange] is null AND t1.[DataSet] = 'webshop'

The desired result is that it inputs the webshop date ranges only if they don't already exist for webshop (this prevents duplicate data if the query is run twice)

DataSet DataRange business 01/02/2013 business 02/02/2013 business 03/02/2013 webshop 01/02/2013 webshop 02/02/2013 webshop 03/02/2013

use a where not exists clause and select from your inserting table joining your table used for insert values making a join on corresponding keys) — Mathese F
– Mathese F, Commented Dec 18, 2013 at 13:34

valex · Accepted Answer · 2013-12-18 13:36:03Z

You should move t1.[DataSet] = 'webshop' condition to the JOIN from the WHERE class

INSERT INTO [ImportedDateRange] ([DataSet],[DateRange]) select DISTINCT 'webshop', cast(T2.[OrderCreatedDate] as DATE) from webshop T2 left join [ImportedDateRange] T1 on cast(T2.[OrderCreatedDate] as DATE) = t1.[DateRange] AND t1.[DataSet] = 'webshop' where t1.[DateRange] is null

Collectives™ on Stack Overflow

SQL INSERT values if they do not already exist

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related