Insert multiple rows, most of them constant

Question

I have to insert big amount of records in a table. It is not quite normalized, so most of the fields are repeated.

I know the proper command is:

INSERT INTO table_name (field1, field2, ..., field_n) 
VALUES (value1, value2, ..., value_n),
    ...
    (value1, value2, ..., value_n)

But I wonder whether it is possible to keep some of the values fixed and just indicate the different ones.

Let's say instead of

INSERT INTO table_name (shop, month, sale)
VALUES (1, 2, 23),
    (1, 2, 28),
    (1, 2, 29),
    (1, 2, 30)

Having something like

INSERT INTO table_name (shop, month, sale)
VALUES (1, 2, 23), ... 28 / 29 / 30

If it is not possible I would create a procedure with a loop, feeding a string, etc. It would not be a big issue, but my point is to know if INSERT INTO has any particularity that allows doing this without procedures.

Answer 1

You can try something like the following:

INSERT INTO table_name (shop, month, sale)
SELECT * FROM
(SELECT 1 as shop, 2 as month) as sm,
(SELECT 23 as sale UNION ALL SELECT 28 UNION ALL SELECT 30) as sales;

Answer 2

You can use the default constraint which will add the default value when you do not specify that in the insert into statement. If you specify a value that value will be added.

Just set the default value for your column in your table

ALTER TABLE tblname ALTER columnName SET DEFAULT 'value'

Refer http://www.w3schools.com/sql/sql_default.asp