[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

add column schema as union to base table/view

Posted on 2011-10-01
6
Medium Priority
?
344 Views
Last Modified: 2012-06-21
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.................................
0
Comment
Question by:25112
  • 3
  • 2
6 Comments
 
LVL 5

Author Comment

by:25112
ID: 36898444
the reason is this is needed for a command line tool which does not output headers (bcp)
0
 
LVL 15

Expert Comment

by:Eyal
ID: 36898455
select 'col1','col2','col3','col4'....
union all
select col1,col2,col3,col4.... from table
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36898508
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:25112
ID: 36913446
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36913527
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
 
LVL 5

Author Comment

by:25112
ID: 36927895
got you..
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question