How to duplicate a table by changing string values to decimal

Question

Good afternoon, you guys.

I am learning SQL and I set out to import a table . csv from the central bank to apply my knowledge. The table consists of four columns:

date entered | name of the currency | value of the currency | quantity of the currency in circulation

The problem is that the quantity came badly formatted (number with semicolons, ex: 5,000,000,000.00 to 5 billion).
The file . csv has 380000 lines, so it is very impracticable to edit manually in the source file. I’ve heard about ETL process, I imagine that maybe it can be solved with this, but as I’m starting my studies in database I have no knowledge about it.

What did I do? By importing, I set everything to string to later change the type of values.

So I have two tables now, the first table would be the original one called circulante1 (columns are all string) and the second table I created called circulante2 (string/string/float/double). I know that the date is not string, but it is so much problem for now that I wanted to solve before the values.

So the only way I thought I could do that (and I thought something was wrong, because it didn’t work) was by using this confusion:

    DELIMITER $$
    SELECT count(*) INTO @linhas FROM circulante1;
    DECLARE @contador INT = 0;
    WHILE @contador < @linhas
    BEGIN
        SELECT data_,nome,cast(valor as float) as valor,cast(replace(replace(qntd,'.',''),',','.') AS DOUBLE) as qntd INTO 
        @data_,@nome,@valor,@qntd
        FROM circulante1
        WHERE PRIMARY = @contador + 1  -- pra ser bem sincero não sei exatamente o que fiz aqui, como não tinha nenhuma coluna auto_increment eu tentei usar isso como o index
        INSERT INTO circulante2 (data_,nome,valor,qntd) VALUES (@data_,@nome,@valor,@qntd) 
        SET @contador = @contador + 1;
    END$$
    DELIMITER ;

Error Code: 1064 You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'DECLARE @counter INT = 0; WHILE @counter < @lines BEGIN SELECT data_,name,ca' at line 1

Basically I have no idea where I went wrong, whether it was logic or a syntax problem. Surely there’s a better way to do that too, if you can point me out I appreciate.

--------------- EDIT 20:47 BRT Guys, Ricardo already answered me, but in case someone can tell me, what I did (despite being inefficient) is wrong where? I would like to understand my mistake to take as learning too

Answer 1

If you have a table with the data in text, just make a INSERT with SELECT converting the data:

INSERT INTO circulante2 
SELECT CAST(data AS DATE) as data, 
       nome,
       CAST(replace(replace(valor, '.',''), ',', '.') as DECIMAL) as valor,
       CAST(qntd AS SIGNED) as qntd
  FROM circulante1;
  

Some remarks:

• type INT is converted with SIGNED/UNSIGNED
• non-integer numbers are converted with CAST to DECIMAL, FLOAT and other types do not work directly with CAST, read more here: https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html

See here an example working: https://www.db-fiddle.com/f/3L6ETu2mZGPX4c4R9jhWuG/0