Insert data into one table by referencing columns from another table in SQL

Question

I have created two tables, one is animation_companies (animation company) and the other is cartoon_characters (cartoon characters). Now I receive a document that says "Simulating a supervisor requesting to insert information into a database I created". The data will look like this:

The animation_companies table will only contain the following three records:

Warner Bros.
Disney
Nickelodeon

The cartoon_characters table will contain only fourteen (14) records, including the character's name, the year it was created, and the company that owns the cartoon character.

Companies "Warner Bros.", "Disney" and "Nickelodeon" are for display purposes only and must be referenced from the animation_companies table.

|Cartoon Name   |Creation Year  |Company
| ------------- | ------------- |----------- |
|Porky Pig      |1935           |Warner Bros.|
|Daffy Duck     |1937           |Warner Bros.|
|Elmer Fudd     |1937           |Warner Bros.|
|Bugs Bunny     |1940           |Warner Bros.|
|Foghorn Leghorn|1946           |Warner Bros.|
|Mickey Mouse   |1928           |Disney      |
|Goofy          |1934           |Disney      |
|Donald Duck    |1931           |Disney      |
|Elsa of Arendelle|2013         |Disney      |
|Anna of Arendelle|2013         |Disney      |
|Doug Funnie    |1991           |Nickelodeon |
|Arnold Shortman|1985           |Nickelodeon |
|Tommy Pickles  |1991           |Nickelodeon |
|Aang           |2005           |Nickelodeon |

使用以下语句创建了animation_companies（动画公司）表：

CREATE TABLE Animation_Companies 
(
 CompanyID INT PRIMARY KEY AUTO_INCREMENT, 
 CompanyName VARCHAR(100) NOT NULL 
); 

The table cartoon_characters was created using this statement:

CREATE TABLE Cartoon_Characters 
(
  cartoonID INT PRIMARY KEY AUTO_INCREMENT, 
  cartoonName VARCHAR(100) NOT NULL, 
  yearCreated INT,        
  OwningCompany INT NOT NULL, 
  CompanyID INT NOT NULL, 
    CONSTRAINT Cartoon_Characters_fk_Animation_Companies 
    FOREIGN KEY (CompanyID) REFERENCES Animation_Companies (CompanyID)
);

我使用以下语句向animation_companies（动画公司）表的记录插入了数据：

INSERT INTO animation_companies(CompanyName) 
VALUES ('Warner Bros.'), ('Disney'), ('Nickelodeon');

我面临的问题如下所示：

"向Cartoon_Characters（卡通角色）表插入数据：

不使用列列表，编写插入语句，插入卡通名称、创建年份和所属公司。

Note:

Enclose the string in single quotes. Pay attention to spaces. Use DEFAULT to automatically increment the value. The company is referenced by the integer ID."

This is the statement I tried:

INSERT INTO cartoon_characters
VALUES (DEFAULT, 'Porky Pig', 1935, 'Warner Bros.', 1),
       (DEFAULT, 'Daffy Duck', 1937, 'Warner Bros.', 1),
       (DEFAULT, 'Elmer Fudd', 1937, 'Warner Bros.', 1),
       (DEFAULT, 'Bugs Bunny', 1940, 'Warner Bros.', 1),
       (DEFAULT, 'Foghorn Leghorn', 1946, 'Warner Bros.', 1),
       (DEFAULT, 'Mickey Mouse', 1928, 'Disney', 2),
       (DEFAULT, 'Goofy', 1934, 'Disney', 2),
       (DEFAULT, 'Donald Duck', 1931, 'Disney', 2),
       (DEFAULT, 'Elsa of Arendelle', 2013, 'Disney', 2),
       (DEFAULT, 'Anna of Arendelle', 2013, 'Disney', 2),
       (DEFAULT, 'Doug Funnie', 1991, 'Nickelodeon', 3),
       (DEFAULT, 'Arnold Shortman', 1985, 'Nickelodeon', 3),
       (DEFAULT, 'Tommy Pickles', 1991, 'Nickelodeon', 3),
       (DEFAULT, 'Aang' 2005, 'Nickelodeon', 3);

Answer 1

One method is to insert the data into a temporary table first.

INSERT INTO #SomeTempTable
VALUES ('Porky Pig', 1935, 'Warner Bros.', 'Warner Bros');

Then connect it with the Animation_Companies table (JOIN).

INSERT INTO cartoon_characters(cartoonName, YearCreated, OwningCompany, CompanyId)
SELECT t.cartoonName, t.YearCreated, t.OwningCompany, a.CompanyId
FROM #SomeTempTable t
JOIN Animation_Companies a ON a.CompanyName = t.CompanyName