I have the following SQL command that I need to save as a csv file with column headers:
mysql > SELECT a.last_name, a.first_name, username, is_active, graduation FROM auth_user a INNER JOIN userprofile_userprofile u ON a.id = u.user_id WHERE a.id>39 AND a.is_active=1 ORDER BY last_name
How would I do this?
If I'm not commiting any syntactical mistake this should work:
SELECT 'LastName', 'FirstName', 'Username', 'IsActive', 'Graduation' UNION ALL SELECT a.last_name, a.first_name, username, is_active, graduation INTO OUTFILE 'yourFile.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' FROM auth_user a INNER JOIN userprofile_userprofile u ON a.id = u.user_id WHERE a.id>39 AND a.is_active=1 ORDER BY last_name
MySQL won't allow you to add column headers so you can hard code them in a union.
You can also check the
SELECT ... INTO syntax here.
UNION ALL, actually. However, why would adding a single record cause issues? Can you please clarify or provide any example or documentation link? Thanks - Mosty Mostacho 2012-04-04 20:30
selectstatement, not actually an insert statement - Mosty Mostacho 2012-04-04 20:51
It appears that MySQL does not natively support the ability to include column headers in an export. A simple google search returned this article which uses stream edit to add columns to an export file.
You may also want to read this SO post (and accepted solution) as it appears to be another option.
Did you try using
INTO OUTFILE 'file_name' export_options?
you can simply use BCP command, in which case you have to enable the xp_cmdshell if you are using SQL mangement studio.
Here is an example
DECLARE @bcpCommand varchar(2000) SET @bcpCommand = 'bcp "SELECT * FROM users" queryout "c:\dump.csv" -U USERNAME -P PASSWORD -c' EXEC master..xp_cmdshell @bcpCommand
Note that the csv file is located in the server where the database is hosted.