CODEWITHSUNDEEP
mysql
bikey77
bikey77

Reputation: 6672

mysql merge scripts to create table

I've came across an issue and I cant think of a way to solve it. I need to insert country names in several languages into a table on my mysql db. I found these links link1 (en) , link2 (de) etc but I dont know how to proceed in order to finally have a table looking like this:

CREATE TABLE `country` (
  `id` varchar(2) NOT NULL,
  `en` varchar(64) NOT NULL,
  `de` varchar(64) NOT NULL,
  ...
  ...
  PRIMARY KEY (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

Upvotes: 0

Views: 132

Answers (2)

Devart
Devart

Reputation: 121922

I can suggest you another table design. Create languages table, and modify a little country table: add lang_id field and create foreign key - FOREIGN KEY (lang_id) REFERENCES languages (id). Then populate languages and country tables.

For example:

CREATE TABLE languages(
  id VARCHAR(2) NOT NULL,
  name VARCHAR(64) NOT NULL,
  PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE country(
  id VARCHAR(2) NOT NULL,
  lang_id VARCHAR(2) NOT NULL DEFAULT '',
  name VARCHAR(64) NOT NULL,
  PRIMARY KEY (id, lang_id),
  CONSTRAINT FK_country_languages_id FOREIGN KEY (lang_id)
  REFERENCES languages (id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB;

-- Populate languages
INSERT INTO languages VALUES 
  ('en', 'English'),
  ('de', 'German');

-- Populate names from 'en' table
INSERT INTO country SELECT id, 'en', name FROM country_en;

-- Populate names from 'de' table
INSERT INTO country SELECT id, 'de', name FROM country_de;

...Where country_en and country_deare tables from your links.

Upvotes: 0

bikey77
bikey77

Reputation: 6672

Well, I finally figured it out so I'm posting to maybe help others.

I created 2 tables (country_en) and (country_de) and then ran the following statement:

DROP table if exists `countries`;

CREATE TABLE `countries` (
  id  varchar(2), el varchar(100), de varchar(100)
);

INSERT INTO `countries`
SELECT country_en.id, el, de
FROM   country_en 
JOIN   country_de ON (country_en.id = country_de.id);

which creates the table countries and joins the other 2 tables on their common key id

Upvotes: 1

Related Questions