SQL Insert multiple rows with one column always the same and one column different

Question

Is there a quick way to insert multiple values into one column while the second column has a different value.

Say I have two columns called Number and Colour. The Number column is always going to be 1 and the Colour column changes.

At the moment i'm doing the following...

INSERT INTO ColourTable(Number, Colour)

SELECT '1' ,'red'

UNION ALL

SELECT '1' ,'yellow'

UNION ALL

SELECT '1' ,'green'

UNION ALL

SELECT '1' ,'blue'

UNION ALL

SELECT '1' ,'orange'

Which is fine if there are just a few inserts to do but the problem is I need to insert about 100 rows with the colour column changing and I was wondering if there was a way to set the number column?

**i think i need to explain myself a little better...

say the colour columns have 40 different colours i need to insert these colours into different rows with the number column saying say 1 to 100 (the number are actually randon codes so incrementing won't work).

So I have to do 40 inserts of the colour rows with the column number = 1 40 inserts with the column number = 2 40 inserts with the column number = 3 and so on to 100

Answer 1

If I'm understanding the question correctly then you're looking for all combinations of your random code field and colour field.

So for example if you had three colours red, green and blue and 3 random codes 1, 14, 25 then you'd like the following set.

1   red
1   green
1   blue
14  red
14  green
14  blue
25  red
25  green
25  blue

If this is the case then you could produce a pair of tables, one with the codes, the other with the colours

CREATE TABLE #Codes(
    [CodeNumber] int NOT NULL
) 

Insert Into #Codes 
Select 1
Union All
Select 14
Union All
Select 25



CREATE TABLE #Colours(
    [Colour] varchar(50) NOT NULL
) 

Insert Into #Colours 
Select 'red'
Union All
Select 'green'
Union All
Select 'blue'

Then use a cross join to return all of the combinations.

Select cd.CodeNumber, cl.Colour
From #Codes cd
    Cross Join #Colours cl

Answer 2

Put them in separate subselects, and allow a cross join (,) to occur:

INSERT INTO ColourTable(Number, Colour)
SELECT Num.n,Col.c FROM
 (select '1') Num(n),

 (select 'red' union all
  select 'yellow' union all
  select 'green' union all
  select 'blue' union all
  select 'orange') Col(c)