Unload to text - 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

General SQL Server Forums

New to SQL Server Programming

Unload to text

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2009-11-20 : 10:11:30

Hi,

Is there a way to unload a query to a text file using sql code, and not the wizard?

Thanks

nguyenl
Posting Yak Master

128 Posts

Posted - 2009-11-20 : 10:12:36

Sorry, I meant is there a way to unload a table to a text file using sql code, and not a wizard.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-20 : 10:16:35

yes

you can use bcp with a QUERYOUT mode

check the documentation on BCP at books online.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 11:13:52

quote:
Originally posted by Transact Charlie

yes

you can use bcp with a QUERYOUT mode

check the documentation on BCP at books online.

Charlie

What type of text file?

"Mr. Charly told me, I though you'd like to know...."

http://artsites.ucsc.edu/GDead/agdl/charlie.html

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

nguyenl
Posting Yak Master

128 Posts

Posted - 2009-11-20 : 12:14:54

Hi,

I am trying to unload into a pipe delimited text file.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 13:23:58

I prefer |~|


CREATE TABLE myTable99 (Col1 char(1), Col2 int, Col3 datetime)
GO

INSERT INTO myTable99(Col1,Col2,Col3)
SELECT 'a',1, GetDate() UNION ALL
SELECT 'b',2, GetDate() UNION ALL
SELECT 'c',3, GetDate()
GO

SELECT * FROM myTable99

DECLARE @cmd varchar(8000), @hostname varchar(256)
SET @cmd = 'bcp ' + db_name() + '.dbo.myTable99 out d:\mytable99.txt -T -S'+@@SERVERNAME+' -c -t"|~|"'
PRINT @cmd

exec master..xp_cmdshell @cmd

-- Make a share on your local machine and call it public and allow full access

SELECT @hostname = hostname from master.dbo.sysprocesses where spid = @@SPID
SET @cmd = 'Copy d:\mytable99.txt \\'+RTRIM(@hostname)+'\Public\*.*'
PRINT @cmd
exec master..xp_cmdshell @cmd
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources