add column schema as union to base table/view

consider a view vw_view1

how can you add a first line to the statement output

select * from vw_view1

so that the output is given as
col1,col2,col3,col4,col5.........................
data,data,data,data,data.................................
data,data,data,data,data.................................
data,data,data,data,data.................................
data,data,data,data,data.................................
LVL 5
25112Asked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
Sorry, I meant to output the view (i.e., vw_view1). :)
How: Right-click database > Tasks > Export Data...

CAST/CONVERT: if you go the UNION route, you could use the INFORMATION_SCHEMA.COLUMNS to pull the column names. You can use this to build a dynamic SQL statement that not only pulls the column names, but also sets up the actual select with CAST(... AS VARCHAR(MAX)) as an example, but then you are dealing with dynamic SQL, conversion format of data, etc. Probably a bigger mess than it is worth quite honestly.

I would try exporting the data OR as I typically do when I just need a spreadsheet is open Excel and do data query from Excel. I test my SQL in SSMS, then just copy and paste it into MS Query. There are a few quirks but in general and especially if dealing with something simple like "select * from vw_view1" this works and is useful to my business users as it is refreshable. This by no means is a silver bullet and should not be used without care as there are things to be careful of when doing heavy analysis using Excel, but it definitely works. :)
0
 
25112Author Commented:
the reason is this is needed for a command line tool which does not output headers (bcp)
0
 
EyalCommented:
select 'col1','col2','col3','col4'....
union all
select col1,col2,col3,col4.... from table
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Kevin CrossChief Technology OfficerCommented:
You may want to consider doing the output view SSIS package or Import/Export Wizard. You can do the UNION, but that requires the data type to match; therefore, you have to CAST/CONVERT all the columns in your data query to VARCHAR to match the column names row.
0
 
25112Author Commented:
thanks-
Eyal, that is not dynamic and also as mwvisa1 said, it is more complex than that.

mwvisa1,
>>output view
did you mean output window?

>>Import/Export Wizard
how?

>>you have to CAST/CONVERT
does it have to be done manually or possible to script it?
0
 
25112Author Commented:
got you..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.