Multiple SQL Query Output in CSV

Approach: We will create a table with multiple columns of any name with datetype as varchar to store any value. Once table is created we will insert the columns header of  the output of your first query then insert the results of the first query. Follow same steps for other queries you want to execute.

For better understanding please check below queries

CREATE TABLE [dbo].[tblABC](
[A] [varchar](200) NULL, [B] [varchar](200) NULL, [C] [varchar](200) NULL, [D] [varchar](200) NULL, [E] [varchar](200) NULL,
[F] [varchar](200) NULL, [G] [varchar](200) NULL, [H] [varchar](200) NULL, [I] [varchar](200) NULL, [J] [varchar](200) NULL,
[K] [varchar](200) NULL, [L] [varchar](200) NULL, [M] [varchar](200) NULL, [N] [varchar](200) NULL, [O] [varchar](200) NULL,
[P] [varchar](200) NULL, [Q] [varchar](200) NULL, [R] [varchar](200) NULL, [S] [varchar](200) NULL, [T] [varchar](200) NULL,
[U] [varchar](200) NULL, [V] [varchar](200) NULL, [W] [varchar](200) NULL, [X] [varchar](200) NULL, [Y] [varchar](200) NULL,
[Z] [varchar](200) NULL
) ON [PRIMARY]
GO


Declare @aa varchar(100)
set @aa =cast(getdate() as varchar)

INSERT INTO [dbo].[tblABC] (A)
(Select 'Report Generated on  '+ @aa)


INSERT INTO [dbo].[tblABC] (A) Values
('Server :  ' + @@SERVERNAME)
INSERT INTO [dbo].[tblABC] (A) Values
('Instance  :  '+ @@SERVICENAME)

INSERT INTO [dbo].[tblABC] (A) values
(' ')

INSERT INTO [dbo].[tblABC] (A) values
('>>>>>>>>>>>>>Instance Logins<<<<<<<<<<<<<<<')

INSERT INTO [dbo].[tblABC] (A) values
(' ')

INSERT INTO [dbo].[tblABC] (A,B,C) Values
('name','dbname','language')

INSERT INTO [dbo].[tblABC] (A,B,C)
(select name,dbname,language  from syslogins)

INSERT INTO [dbo].[tblABC] (A) values
(' ')

INSERT INTO [dbo].[tblABC] (A) values
('>>>>>>>>>>>>>Database details<<<<<<<<<<<<<<<')

INSERT INTO [dbo].[tblABC] (A) values
(' ')

INSERT INTO [dbo].[tblABC] (A,B,C,D,E,F,G,H,I,J) Values
('name','database_id','create_date','compatibility_level','collation_name','user_access_desc','state_desc',
'snapshot_isolation_state','recovery_model','recovery_model_desc')

INSERT INTO [dbo].[tblABC] (A,B,C,D,E,F,G,H,I,J)
(Select name,database_id,create_date,compatibility_level,collation_name,user_access_desc,state_desc,snapshot_isolation_state,
recovery_model,recovery_model_desc from sys.databases)

Inserted Data will look like this

Select * from [dbo].[tblABC]

To avoid null run below query

SELECT isnull([A] ,'') as A      ,isnull([B] ,'') as B      ,isnull([C] ,'') as C      ,isnull([D] ,'') as D      ,isnull([E] ,'') as E
      ,isnull([F] ,'') as F      ,isnull([G] ,'') as G      ,isnull([H] ,'') as H      ,isnull([I] ,'') as I      ,isnull([J] ,'') as J
      ,isnull([K] ,'') as K      ,isnull([L] ,'') as L      ,isnull([M] ,'') as M      ,isnull([N] ,'') as N      ,isnull([O] ,'') as O
      ,isnull([P] ,'') as P      ,isnull([Q] ,'') as Q      ,isnull([R] ,'') as R      ,isnull([S] ,'') as S      ,isnull([T] ,'') as T
      ,isnull([U] ,'') as U      ,isnull([V] ,'') as V      ,isnull([W] ,'') as W      ,isnull([X] ,'') as X      ,isnull([Y] ,'') as Y
      ,isnull([Z] ,'') as Z
  FROM [master].[dbo].[tblABC]

Output will be like this

Now run below query in PowerShell which will export above data to CSV and skip the columns named A,B,C,D......

$Query = "
SELECT isnull([A] ,'') as A      ,isnull([B] ,'') as B      ,isnull([C] ,'') as C      ,isnull([D] ,'') as D
      ,isnull([E] ,'') as E      ,isnull([F] ,'') as F      ,isnull([G] ,'') as G      ,isnull([H] ,'') as H
      ,isnull([I] ,'') as I      ,isnull([J] ,'') as J      ,isnull([K] ,'') as K      ,isnull([L] ,'') as L
      ,isnull([M] ,'') as M      ,isnull([N] ,'') as N      ,isnull([O] ,'') as O      ,isnull([P] ,'') as P
      ,isnull([Q] ,'') as Q      ,isnull([R] ,'') as R      ,isnull([S] ,'') as S      ,isnull([T] ,'') as T
      ,isnull([U] ,'') as U      ,isnull([V] ,'') as V      ,isnull([W] ,'') as W      ,isnull([X] ,'') as X
      ,isnull([Y] ,'') as Y      ,isnull([Z] ,'') as Z
  FROM [master].[dbo].[tblABC]
"
$filee="C:\"+"$(get-date -f MM-dd-yyyy)_Report.csv"
write-host $filee
$results = Invoke-SQLcmd -Server CHRV4204 -Database master -Query $Query |
ConvertTo-Csv -NoTypeInformation |Select-Object -Skip 1 |Set-Content -Path $filee

Above script can be execute as job in SQL Server as below

To send attachment via email you can use below script

declare @aa varchar(333) set @aa= convert(varchar, getdate(), 110) select @aa = 'C:\Data\' + @aa + '_Report.csv' select @aa EXEC msdb.dbo.sp_send_dbmail @profile_name = 'TestMailprofile', @recipients ='youremail@gmail.com', @subject = 'test', @body = 'hi', @importance = 'HIGH', @file_attachments = @aa, @body_format ='HTML' --because

In Excel your output will be like this