CODEWITHSUNDEEP
mysqlload-data-infile
kwangil lee
kwangil lee

Reputation: 13

MySQL load data insert, split values to multiple rows

Using the LOAD DATA LOCAL INFILE

num column Store every 6 digits

How can I parameterize the load command to iterate through the array?

Mysql version 8.0.23

In File text

id length 1,
cnt length 2,
num length 500
--------------------
1 3AB1001AB1002AB1003

table A

id cnt num
1 3 AB1001AB1002AB1003

i want output like this table

table B

id cnt num
1 3 AB1001
1 3 AB1002
1 3 AB1003

Upvotes: 1

Views: 135

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562911

I was able to test this table:

create table mytable (
  id int,
  cnt int,
  num varchar(500)
);

I put the line of text you show into a file p.csv:

1 3AB1001AB1002AB1003

I ran the mysql client with the option to enable local data loading:

$ mysql --local-data

Then I loaded the data file this way:

mysql> load data local infile 'p.csv' into table mytable (@temp)
set id = substr(@temp, 1, 1),
    cnt = substr(@temp, 2, 2),
    num = substr(@temp, 3);

It worked!

mysql> select * from mytable;
+------+------+---------------------+
| id   | cnt  | num                 |
+------+------+---------------------+
|    1 |    3 | 3AB1001AB1002AB1003 |
+------+------+---------------------+

Read more about the LOAD DATA INFILE statement here: https://dev.mysql.com/doc/refman/8.0/en/load-data.html

And read about the SUBSTR() function here: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substr

Upvotes: 0

netragon
netragon

Reputation: 1

You can use a trigger to split a row from table 'a' into 3 rows for 'b'. The trigger is following:

`CREATE TRIGGER triger1 AFTER INSERT ON a FOR EACH ROW BEGIN
SET @i = 0;
REPEAT
SET @part = SUBSTRING(NEW.num, 1 + @i*6, 6);
INSERT INTO b VALUE(NEW.id, NEW.cnt, @part);
SET @i = @i + 1;
UNTIL @i = NEW.cnt
END REPEAT;
END;`

Before insert data using the LOAD DATA LOCAL INFILE, you have to create a trigger on table 'a'. Regards.

Upvotes: 0

Related Questions