How do I SQL INSERT INTO two different arrays into a table?-CodePudding

It's been a long time since I've messed with SQL and I'm having to get back into it as I'm building my own video game. I'm using MySQL and am running into a bit of a complex issue here.

Here 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 races and 12 classes. I am wanting code out an INSERT INTO statement using these arrays so that I don't have to manually enter in 108 rows.

Here 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 am needing to loop this INSERT INTO statement until I've gone through all 108 combinations of races and classes. So Dwarf will be in the database as every single class. Then Elf will be inserted to the database for every single class. Then Halfling, then Human, ect, ect.

The Class array is simply going into the class_description and on the class_icon where you see I am dropping the link to the image. Race will follow the race_icon image as well.

Does anyone have any idea on how I can loop through ArrayofRaces 12 times per race so that I can get both classes and races imported with ease?

Thanks in advance!

CodePudding user response：

You can produce all the combinations using a cross join. 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 running example at DB Fiddle.

Note: This example includes two races and two classes to produce a total of 4 combinations. Add the rest and the query will produce all 108 of them.