headers and footers - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

headers and footers

Author

Topic

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2008-06-19 : 09:06:59

Hi all,

I am sure this is an easy question but I cant work it out.

I need to output the results of a table to file. I have set up a job to do this, as it needs to be updated every night.

I just do a select * from table

Howerver I dont want the coloum names at the top or the -------'s or the (1) rows bla bla

Bit.

Any ideas?

Dave

Dave Dunckley says there is a law for the rich and a law for the poor and a law for
Dirty Davey.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 09:09:11

It you use OPENROWSET, you can use the text file as any other table.

First truncate the table, and then insert. No headers are inserted.

E 12°55'05.25"
N 56°04'39.16"

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2008-06-19 : 09:10:52

Ah sorry,

I worder that badly. I need to get the table into a file, not the other way round.

Dave Dunckley says there is a law for the rich and a law for the poor and a law for
Dirty Davey.

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2008-06-19 : 09:16:40

Ok worked out how to get rid of the number of rows affected:-

set nocount on

Just not sure how to get rid of the colum headings and all the ---------'s

Dave Dunckley says there is a law for the rich and a law for the poor and a law for
Dirty Davey.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 09:18:17

This is for Excel, but the technique for a text file is the same.

-- Empty file first
DELETE	t
FROM	OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;', 'SELECT * FROM [SheetName$]') AS t

-- Populate file
INSERT	OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;', 'SELECT * FROM [SheetName$]')
SELECT	*
FROM	MySQLServerTable

E 12°55'05.25"
N 56°04'39.16"

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2008-06-19 : 09:25:24

Thanks peso. But its the other way I need to go.

I need to chuck data from my table to a txt file.

Dave Dunckley says there is a law for the rich and a law for the poor and a law for
Dirty Davey.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 09:33:57

What other way?

The code examples I gave copies records from your SQL Server table and inserts them into a text file.

E 12°55'05.25"
N 56°04'39.16"

dirtydavey
Yak Posting Veteran

80 Posts

Posted - 2008-06-19 : 09:43:03

O right yeah sorry might be going mad. Thanks

Not sure this is quite what I need though.

Do you know how to just turn off that header?

Dave Dunckley says there is a law for the rich and a law for the poor and a law for
Dirty Davey.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 10:09:14

An INSERT statement never includes the header.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources