June 6, 2012 at 3:05 pm
I have several vendors whom need data extracts from my SQL box as text data, but with multiple record types in the data. Ie a group heading record, with its detail lines following, and of course over all header and detail records. Is there any other way to do this than the classic manual concating of the fields? For example,
Select Col1 + Col2 + Col3 as Data From FileHeaders Union All
Select Col1 + Col2 + ... Col12 as Data From GrpHeaders Union All
Select Col1 + Col2 + ... Col25 as Data From Details Union All
Select Col1 + Col2 + ... Col8 as Data From GrpTrailer Union All
Select Col1 + Col2 + Col3 as Data From FileTrailers
Followed by using a BCP out or such on the resultset?
Some of these vendors can have hundreds of fields in their layouts, manually contatenating all of these does not seem like it would be the best way to handle such things.
Thanks!
June 6, 2012 at 3:19 pm
this should get you close to what you are looking for.
SELECT
'Select '+
substring((SELECT ( ', ' + name )
FROM sys.columns t2
WHERE t1.object_id = t2.object_id
ORDER BY
name
FOR XML PATH( '' )
), 3, 1000 ) +' From '+object_name(t1.object_id) +' UNION ALL'
FROM sys.tables t1
where name in ('FileHeaders','GrpHeaders','Details')
GROUP BY t1.object_id
June 6, 2012 at 5:46 pm
Is SSIS an option? It is built to make tasks like this relatively simple.
This thread runs down the issue for someone else in your situation fairly well.
Creating Header AND Footer to a flat file destination file
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2012 at 7:54 am
Thanks for the idea's from both of you. I like having more than one tool for the task. So for those SSIS is not the best choice XML path should work well.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply