Insert many rows in to one column with same string in another column

Question

I am trying to get this query to work

INSERT INTO [ImportedDateRange] ([DataSet],[DateRange]) 
'webshop', (select DISTINCT cast([OrderCreatedDate] as DATE) from webshop)

I basically want it to look something like this:

DataSet     DateRange
webshop     01/10/2013
webshop     02/10/2013
webshop     03/10/2013
webshop     03/10/2013

where webshop is entered each time but each date range is copied over in to a new row.

Also to check with the DateRange records for DataSet webshop already exist

thanks for any help and advice

Make sure you put all your requirements in the question... not as comments to answers!! So that everyone can see :) — huMpty duMpty
– huMpty duMpty, Commented Dec 18, 2013 at 9:43

Deepshikha · Accepted Answer · 2013-12-18 09:26:51Z

2

To insert unique DateRange records for DataSet webshop in [ImportedDateRange] table from webshop table write as:

INSERT INTO [ImportedDateRange] 
([DataSet],[DateRange]) 
select DISTINCT 'webshop', cast(T2.[OrderCreatedDate] as DATE) from webshop T2
WHERE NOT EXISTS (select 1 from [ImportedDateRange] T1 where T1.[DateRange] = T2.[OrderCreatedDate])

answered Dec 18, 2013 at 9:26

Deepshikha

10.3k2 gold badges24 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Deepshikha Over a year ago

This query will only insert unique records in ImportedDateRange table from webshop table which are not already in ImportedDateRange table. This is what the user wants...

huMpty duMpty Over a year ago

@AntoRajaPrakash: OP states this requirement on one of the comment not in the question!

Anto Raja Prakash Over a year ago

'INSERT INTO [ImportedDateRange] ([DataSet],[DateRange]) select DISTINCT 'webshop', cast(T2.[OrderCreatedDate] as DATE) from webshop T2 left join [ImportedDateRange] T1 on t2.[OrderCreatedDate] = t1.[DateRange] where t1.[DateRange] is null ' check this too an alternate with improved performance

Kevin Holditch · Accepted Answer · 2013-12-18 09:16:01Z

2

INSERT INTO [ImportedDateRange] ([DataSet],[DateRange]) 
SELECT DISTINCT  'webshop', CAST([OrderCreatedDate] as DATE) 
FROM   webshop

answered Dec 18, 2013 at 9:16

Kevin Holditch

5,3534 gold badges22 silver badges37 bronze badges

5 Comments

neeko Over a year ago

another quick question, would you know how to get it to check with the DateRange records for DataSet webshop already exist?

Kevin Holditch Over a year ago

Sorry I don't understand what you are asking?

neeko Over a year ago

if I ran that query twice it would insert the same data twice, any way to prevent this?

Kevin Holditch Over a year ago

INSERT INTO [ImportedDateRange] ([DataSet],[DateRange]) SELECT DISTINCT 'webshop', CAST([OrderCreatedDate] as DATE) FROM webshop WHERE DateRange NOT IN (SELECT DISTINCT CAST([OrderCreatedDate] as DATE) FROM webshop)

huMpty duMpty Over a year ago

@KevinHolditch: No, you need to put a where condition on the select statement against the same table to ignore the existing values

huMpty duMpty · Accepted Answer · 2013-12-18 09:16:14Z

1

INSERT INTO [ImportedDateRange] 
([DataSet],[DateRange]) 
select DISTINCT 'webshop', cast([OrderCreatedDate] as DATE) from webshop

answered Dec 18, 2013 at 9:16

huMpty duMpty

14.5k14 gold badges64 silver badges102 bronze badges

Comments

Jonathan · Accepted Answer · 2013-12-18 09:17:08Z

1

'webshop' should be within the SELECT, try running the select on it's own as the output will be the same as will be inserted into the ImportedDateRange table.

INSERT INTO [ImportedDateRange] 
([DataSet], [DateRange]) 
select DISTINCT 'webshop', cast([OrderCreatedDate] as DATE) from webshop;

answered Dec 18, 2013 at 9:17

Jonathan

1,83314 silver badges15 bronze badges

Collectives™ on Stack Overflow

Insert many rows in to one column with same string in another column

4 Answers 4

3 Comments

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related