Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Get column names in first row of result set Expand / Collapse
Author
Message
Posted Thursday, August 16, 2012 12:59 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:56 AM
Points: 139, Visits: 385
Thanks for the ideas Sean and Lynn. I'm actually helping another DBA. There are a few other requirments such as stripping out some characters and he wrote a query to accomplish that. So I was trying to make it work with his query.

But *duh* I can drop his query in the export wizard and check that box to include column headers. Going to try that now...
Post #1346202
Posted Thursday, August 16, 2012 1:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:53 PM
Points: 5,383, Visits: 7,456
I would agree with SSIS in general, but if that's not an option and you must stay in T-SQL the easiest way is to quick-hack the results for BCP, like so (see the second post down):

http://stackoverflow.com/questions/1355876/export-table-to-file-with-column-headers-column-names-using-the-bcp-utility-an

It's similar to the above discussed union but it's forced into BCP without having to fight with sorting components.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1346210
Posted Tuesday, July 29, 2014 11:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:34 AM
Points: 1, Visits: 4
Hi,

I had the same issue from the same type of client.
I sorted it out with a bit of dynamic SQL to create the Union query.

Note: because all the data must be converted to varchar due to the new first line containing column titles, formats need to be taken into account (for dates at least).

Declare @TableName nvarchar(max)
Declare @ListColumns nvarchar(max)
Declare @ListColumnsConverted nvarchar(max)
Declare @QueryColumnNames nvarchar(max)
Declare @QueryTableValues nvarchar(max)
Declare @QueryHeaderAndRows nvarchar(max)

Set @TableName = 'YourTable'

--Get the column hearders from the system table
SELECT @ListColumns = coalesce(@ListColumns + ', ', '') + convert(varchar(500),COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION

--Get the same list of column from the system table but adding a convert statement (All columns must be converted to varchar because of the first row containing only alphanumeric)
SELECT @ListColumnsConverted= STUFF((SELECT ',' + Case When DATA_TYPE = 'datetime' Then
'Convert(VARCHAR(11), ' + convert(varchar(max), COLUMN_NAME) + ',103)'
Else
'Convert(varchar(max),' + convert(varchar(max), COLUMN_NAME) + ')'
End
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION
FOR XML PATH('')), 1, 1, '')

-- Create the Select statement containing only the headers names
SET @QueryColumnNames = 'Select ''' + Replace(@ListColumns, ', ', ''', ''') + ''''

-- Creating the statement with the values
SET @QueryTableValues = 'Select ' + @ListColumnsConverted + ' From ' + @TableName

-- Union all on both
SET @QueryHeaderAndRows = @QueryColumnNames + ' Union All ' + @QueryTableValues

EXEC (@QueryHeaderAndRows)

Post #1597417
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse