how to include column names in a bcp operation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

how to include column names in a bcp operation

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-11-01 : 13:57:22

Hi I need to output the result of a query
to an excel sheet using bcp, its working fine except I dont know how to include the headings(column names)

hope somebody can help

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-01 : 14:24:43

of course:
http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

heze
Posting Yak Master

192 Posts

Posted - 2006-11-01 : 14:33:33

thanks, spirit, I was hoping I didnt have to do that, maybe I will have to evaluate a DTS package but IIm not to fond of it

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-01 : 14:49:25

just use the sporc.
what's the problem?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

heze
Posting Yak Master

192 Posts

Posted - 2006-11-01 : 15:26:09

S
how about using

select sc.*
FROM tempdb..sysobjects so inner join tempdb..syscolumns sc
on so.[id]=sc.[id]
where so.[id]=object_id('tempdb.dbo.#t')
order by colorder

instead of information schema, to avoid the global temps, or is theree a way to work with local temps and information schema?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-01 : 16:05:17

the reason for using global ##temps is because they can be accessed with dynamic as well as non dynamic sql while
normal #temps can't.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

heze
Posting Yak Master

192 Posts

Posted - 2006-11-01 : 16:11:26

yes they can S
create table #myTemp(
fields datatypes
)
exec('select * from #myTemp')
although you do lose the fast track of the "into"

heze
Posting Yak Master

192 Posts

Posted - 2006-11-01 : 16:13:38

this is what ive got so far, not as flexible as yours and still hardcoded but it leaves my tables ready to bcp with column names

drop table #potgradsSchemaPh
drop table #fieldsCatalog

select *
into #potgradsSchemaPh
from POT_potGradByCollege('BU') order by entrytermcode,CollegeCurrentTerm,MajorCurrentTerm,DepartmentCurrentTerm,PIDM

select identity(int,0,1) as myId,sc.[name]
into #fieldsCatalog
FROM tempdb..sysobjects so inner join tempdb..syscolumns sc
on so.[id]=sc.[id]
where so.[id]=object_id('tempdb.dbo.#potgradsSchemaPh')
order by colorder

declare
@fieldIterator as integer,
@sqlstrAccum as varchar(8000),
@sqlstrValuesAccum as varchar(8000),
@fieldPlaceHolder as varchar(70)
set @fieldIterator=0
set @sqlstrAccum='insert into #potgradsSchemaPh ('
set @sqlstrValuesAccum='values ('

while(@fieldIterator<(select max(myId) from #fieldsCatalog))
begin
set @fieldPlaceHolder=(select [name] from #fieldsCatalog where myId=@fieldIterator)
set @sqlstrAccum=@sqlstrAccum+'['+@fieldPlaceHolder+']'
set @sqlstrValuesAccum=@sqlstrValuesAccum+''''+@fieldPlaceHolder+''''

if(@fieldIterator<(select max(myId) from #fieldsCatalog)-1)
begin
set @sqlstrAccum=@sqlstrAccum+','
set @sqlstrValuesAccum=@sqlstrValuesAccum+','
end
--print @sqlstrAccum
set @fieldIterator=@fieldIterator+1
end

exec (@sqlstrAccum+') ' +@sqlstrValuesAccum+')')

heze
Posting Yak Master

192 Posts

Posted - 2006-11-01 : 23:48:37

ok I solved the problem now, I have another, How to format the target excel file,
Ive used visual basic and it was pretty straight forward, however, I would like to do everything from sql server so what do you recomend?
I am loking at format files for bcp but it seeems only work for test files not for excel,
I tried to format the files that bcp creates but when I run bcp, they are recreated and the ugly format is again present,
specifically, I would like to
1-make the excel cells open as wide as necessary to show the entire value of the field
2-colour the first row (fieldnames)
3-Be able to bcp more than one sql server result set into one single excel file, only uing differeent tabs,

hope somebody has had a similar problem before, hope I dont have to go to the open rowset or open query options

thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-02 : 04:48:23

i was playing with that with no success.
so in the end i varchar-ed everything.


-- this fails
exec('select * into #temp from orders')
select * from #temp 
drop table #temp 
go
-- while this works
select * into #temp from orders
exec('select * from #temp')
select * from #temp 
drop table #temp

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

heze
Posting Yak Master

192 Posts

Posted - 2006-11-02 : 10:47:51

this one works S,

drop table #t
create table #t
(
fi1 varchar(20),
fi2 varchar(20)
)

insert into #t values ('the','who')
exec ('insert into #t values (''the'',''beatles'')')

select * from #t

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-02 : 10:51:43

of course it does.

but the other way around doesn't and the other way is the way i need it

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources