SQL Inserting multiple rows with one column is always the same and one column is different

Question

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

Let's say I have two columns named Number and Colour . The Number column will always be 1, and the Colour column will change.

Currently I am 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 good if there are only a few inserts, but the problem is that I need to insert about 100 rows with changing the color column, and I was wondering if there is a way to set the column of numbers?

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

say that in the columns of the color 40 different colors I need to insert these colors in different lines with the column number saying from 1 to 100 (the number is actually randon code, so the increment will not work).

So I need to do 40 inserts of color rows with column number = 1 40 inserts with column number = 2 40 inserts with column number = 3, etc. Up to 100

Answer 1

If I understand the question correctly, you are looking for all combinations of your random code field and color field.

So, for example, if you had three colors: red, green and blue, as well as 3 random codes 1, 14, 25, then you need the following set.

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

If so, you can create a pair of tables, one with codes, the other with colors

 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 cross join to return all combinations.

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

Answer 2

Put them in separate subqueries and enable cross join ( , ):

 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) 

Answer 3

Maybe you can set the DEFAULT value of the Number column to 1 before inserting your rows and deleting it later?

Answer 4

 INSERT INTO ColourTable(Number, Colour) SELECT '1' , Col.c FROM (select 'red' union all select 'yellow' union all select 'green' union all select 'blue' union all select 'orange') Col(c)