How to insert two different arrays into a table with SQL INSERT INTO statement?

Question

It's been a long time since I messed with SQL and had to start working with it all over again when I built my own video game. I'm using MySQL and running into some complex issues here.

These are my two arrays;

SET @myArrayofRaces = '"Dwarf", "Elf", "Halfling", "Human", "Dragonborn", "Gnome", "Half-Elf", "Half-Orc", "Tiefling"';
SET @myArrayofClasses = '"Barbarian", "Bard", "Cleric", "Druid", "Fighter", "Monk", "Paladin", "Ranger", "Rogue", "Sorcerer", "Warlock", "Wizard"';

As we can see, I have 9 games and 12 classes. I want to use these arrays to code an INSERT INTO statement so I don't have to enter 108 lines manually.

This is the INSERT INTO statement I am running;

INSERT INTO world_content.character_create_template (
        race,
        aspect,
        instance,
        pos_x,
        pos_y,
        pos_z,
        orientation,
        faction,
        autoAttack,
        race_icon,
        class_icon,
        race_description,
        class_description,
        isactive,
        respawnInstance,
        respawnPosX,
        respawnPosY,
        respawnPosZ,
        startingLevel,
        sprint 
    )
VALUES
    (
        437,
        428,
        29,
        - 170,
        74,
        154,
        0,
        1,
        - 1,
        "Assets/Resources/Assets/Icons/Race Icons/Dwarf.png",
        "Assets/Resources/Assets/Icons/Class Icons/Druid.png",
        "Dwarf",
        "Druid",
        1,
        29,
        - 170,
        74,
        154,
        1,
        - 1 
    )

I need to loop through this INSERT INTO statement until I have completed all 108 combinations of race and class. Therefore, dwarves will appear in the database as each category. Then the Elf will be inserted into each class in the database. Then halflings, then humans, and so on.

The classes array just goes into class_description and class_icon and you can see I'm removing the link to the image. The race will also follow the race_icon image.

Does anyone know how to loop through the ArrayofRaces 12 times per race so I can easily import the categories and races?

Thanks in advance!

Answer 1

You can use cross joins to generate all combinations. For example:

INSERT INTO character_create_template (
        race,
        aspect,
        instance,
        pos_x,
        pos_y,
        pos_z,
        orientation,
        faction,
        autoAttack,
        race_icon,
        class_icon,
        race_description,
        class_description,
        isactive,
        respawnInstance,
        respawnPosX,
        respawnPosY,
        respawnPosZ,
        startingLevel,
        sprint 
    )
with
race as (
  select 'Dwarf' as name
  union all select 'Elf' -- repeat this line for more races
),
class as (
  select 'Barbarian' as name
  union all select 'Bard' -- repeat this line for more classes
)
select 
        437,
        428,
        29,
        - 170,
        74,
        154,
        0,
        1,
        - 1,
        "Assets/Resources/Assets/Icons/Race Icons/Dwarf.png",
        "Assets/Resources/Assets/Icons/Class Icons/Druid.png",
        r.name,
        c.name,
        1,
        29,
        - 170,
        74,
        154,
        1,
        - 1 
from race r
cross join class c

See the running example at DB Fiddle.

NOTE: This example includes two races and two levels, resulting in a total of 4 combinations. Add the rest and the query will generate all 108.