SQL insert query for multiply rows with shared values

Question

I have a simple table Users (name, city, country) and need to add several rows where some of values are the same (city, country). Is there a better way to insert data beside:

insert into Users (name, city, country) values 
("John", "Paris", "France"), 
("Anna", "Paris", "France"), 
("Peter", "Paris", "France"), 
("Mary", "Paris", "France")

Thank you

Generally no, but if you're not using it as manual queries, you should use Prepared Statements. Whatever your project's language is, it has them. You can just set the parameters for city and country once and loop (set and insert). That would be better for your website/application, plus Prepared Statements guarantee you safety against SQL injection. — coladict, Jul 15 '16 at 14:35

score 2 · Answer 1 · answered Jul 15 '16 at 14:34

You can use a query like the following:

insert into Users (name, city, country) 
select name, city, country
from (select 'John' as name union all 
      select 'Anna' union all 
      select 'Peter' union all 
      select 'Mary') as t1
cross join (select 'Paris' as city, 'France' as country) as t2

score 0 · Answer 2 · answered Jul 15 '16 at 14:35

0

You should be able to use variables. So it would look like...

set @city="Paris";

set @country="France";

insert into Users(name, city, country) values
("John", @city, @country)

answered Jul 15 '16 at 14:35

Miniversal

129
2
13

score 0 · Answer 3 · answered Jul 15 '16 at 14:35

Although you could simplify this somewhat by using variables in your particular RDBMS syntax, you are stuck with inserting the same values into multiple rows of the table due to a design decision that you made when defining your Users table.

The problem is that your table is not normalized, meaning that the same information is present multiple times. A proper way of fixing this would be defining a table of countries, a table of cities referencing it, and changing Users table to reference Cities.

score 0 · Answer 4 · answered Jul 15 '16 at 16:56

Your solution is correct normaly but try to replace " by '   else try it:

insert into Users (name, city, country) 
select *
from (
select 'John', 'Paris', 'France' union all 
select 'Anna', 'Paris', 'France' union all 
select 'Peter', 'Paris', 'France' union all  
select 'Mary', 'Paris', 'France'
) tmp

SQL insert query for multiply rows with shared values

4 Answers4