How to save query results to blob field

[xxoulmate]

saving query results to a file
select * from table into outfile savepath

is it possible to save the query result directly to blob field in a table
e.g. something like below
insert into desitination_table_name (blobfield) select * from table

[Atli]

Hey.

You can simply CONCAT all the fields together, which would make them one long string that could be put into a BLOB.

I would question the wisdom of this, though. You would essentially be duplicating data, which is never good.

What exactly are you trying to achieve? Maybe we can offer an alternative.

[xxoulmate]

Current procedure
step 1: (put the query results in a file)
- select * from table into outfile 'c:\test.csv'
step 2: (upload the file in a blob)
- insert into tablename (blob) select load_file('c:\test.csv')

the main goal is to shorten the process.,
instead of doing two steps do it in just one procedure.

[Atli]

Ok, how about this.

You can create a comma-separated list of all the values in a row by doing something like this.

SELECT CONCAT_WS(',', field1, field2, fieldN) FROM tbl

This will give you each row, represented as a comma-separated string.

By combining the GROUP_CONCAT function with a GROUP BY NULL clause, the function will give you a single string, created from the value of the selected fields from all rows.

SELECT GROUP_CONCAT(field1 SEPARATOR '\n')
FROM tbl
GROUP BY NULL

Combine the two, using the result of the CONCAT_WS as the input field for the GROUP_CONCAT function, and you can create a CSV-like string made out of all rows in the table. Use that as the data source for your INSERT query and you should get what you need.

Note that the CSV-like string this generates is not perfect. It will not take into account things like special characters in the values for the fields; both the new-line and double-quote characters will mess up the format of the string.

Also note that duplicating the data like this is usually not a good idea. Can you explain why you need to do this? We may be able to offer a better alternative.