MySQL concatenation

If you want to generate multiple statements for all the tables in MySQL database to insert data imported by Oracle, you can do so by using MySQL concat() with the required information from information_schema database:-

use information_schema;
SHOW TABLES;
desc columns
DESC TABLES;

Say while importing data from .csv files containing data from oracle has some blank values which has to be entered as null value in MySQL database tables, then you have got to check which columns are nullable & accordingly declare set command for those columns for each table in following manner.

mysql> LOAD DATA LOCAL INFILE '/home/../mytbldata.csv' INTO TABLE <db_name>.<tbl_name>
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(<col1_name>,<@var1>, <col3_name>,<@var2>)
SET col2_name = IF(@var1=0, NULL, @var1),
col3_name = IF(@var2=0, NULL, @var2)

1. You can check the nullable columns for a particular table by using foll command:-

select column_name, is_nullable from information_Schema.columns
where is_nullable = 'yes' and table_Schema='<db_name>' and table_name='<table_name>';


2.  Generating LOAD Statements without SET for all tables of a database:-
select concat
('load data local infile \'/home/meena/Desktop/meena/', UPPER(table_name), '_DATA.csv\'
into table <db_name>.', table_name,
' fields terminated by \',\' enclosed by \'"\'
lines terminated by \'\\n\' ignore 1 lines;'  )
AS Load_statement
from information_schema.TABLES
where table_schema='<db_name>' ;

3. Now, the Load statements for all tables in database with set for nullable columns in each table.

select concat
("load data local infile
'/home/meena/Desktop/meena/", upper(table_name),
"_DATA.csv' into table ",table_name, "fields terminated by ','
enclosed by '""' lines terminated by '\\n' ignore 1 lines (")
as my_stmt
from information_schema.tables
where table_name = '<table_name>' and table_schema = '<db_name>'
union select concat(if(is_nullable='YES', '@', ''),column_name)
from columns
where table_name = '<table_name>' and table_schema = '<db_name>' and ordinal_position = 1
union
select concat(',',if(is_nullable='YES', '@', ''),column_name)
from columns
where table_name = '<table_name>' and table_schema = '<db_name>' and ordinal_position > 1
union
select ') set '
from dual
union
select concat(column_name, '= if(@', column_name, '=0, null, @', column_name, ')')
from columns
where table_name = '<table_name>' and table_schema = '<db_name>' and is_nullable = 'YES';

#----------------------------------------------------------------------------------------------------------------
We can use group_concat() function also to generate load data statement with set condition for all tables in a database.


select 
concat("load data local infile '/home/meena/Desktop/meena/dummy_file_format/", UPPER(c.table_name)
, "_DATA.csv.txt' into table ", c.table_name, " fields terminated by ','
enclosed by '\"' lines terminated by '\\n' ignore 1 lines ("
      ) as s1, 

group_concat( if(is_nullable="YES", "@", ""),column_name) as s2, 

concat(") set ") as s3,

group_concat(if(is_nullable="YES", 
           concat(column_name,"= if(@",column_name, "=0, null, @", column_name, ") \n") ,
            ''
         )
       ) as s4

from information_Schema.columns c 
where c.table_schema='dummy_db' group by c.table_name;