MySQL Multi-Insert – Insert Fixed Column Value for Each Row

MySQL

In mysql when I'm doing 

# fr-FR.sql
INSERT INTO table (resource_key, resource_value, locale) VALUES 
    ("GEN.YES", "Oui", "fr-FR"),
    ("GEN.NO", "Non", "fr-FR"),
    ("GEN.MAYBE", "Peut-être", "fr-FR")
ON DUPLICATE KEY UPDATE `resource_value` = VALUES(`resource_value`);

# en-US.sql
INSERT INTO table (resource_key, resource_value, locale) VALUES 
    ("GEN.YES", "Yes", "en-US"),
    ("GEN.NO", "No", "en-US"),
    ("GEN.MAYBE", "Maybe", "en-US")
ON DUPLICATE KEY UPDATE `resource_value` = VALUES(`resource_value`);

Here, 'resource_key' and 'locale' are the primary key. 'locale' is always the same in one file. But differ from file to file (can't set as default value);

This query works. It insert each row and if a label for a given resource_key/locale pair exists, it will update its associated resource_value

Is it possible to insert same value in each row for a given column. There is thousands of lines in different files (added continuously) and always typing the same string in each row is annoying.

Best Answer

I am surely too late for you, but maybe somebody will have similar question in the future.

A way how to accomplish that is by creating temporary table #temptable, and while copying data to actual table, add constant column:

# en-US.sql
INSERT INTO #temptable (resource_key, resource_value) VALUES 
    ("GEN.YES", "Yes"),
    ("GEN.NO", "No"),
    ("GEN.MAYBE", "Maybe")
ON DUPLICATE KEY UPDATE `resource_value` = VALUES(`resource_value`);

INSERT INTO table (resource_key, resource_value, locale) VALUES 
    (SELECT t.*, "en-US" FROM #temptable AS t);
Related Question