Out Statement - 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

Out Statement

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-02-12 : 12:41:34

Hi -

I am trying to output data to a file but cant get this to work:

select *

into OUT C:\Temp\prem.txt

from ##Prem

SQL apparently does not like the "\" in the file path. Is there another way to do this?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-02-12 : 13:19:39

quote:
Originally posted by jcb267

Hi -

I am trying to output data to a file but cant get this to work:

select *

into OUT C:\Temp\prem.txt

from ##Prem

SQL apparently does not like the "\" in the file path. Is there another way to do this?

I don't think the syntnax you are using works with SQL Server. Are you trying to use BCP? If you are the command should start with the keyword bcp and it should be run from a command window. There are examples here: https://msdn.microsoft.com/en-us/library/ms162802.aspx

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-02-12 : 13:59:30

Apparently it doesn't, I still cant get it to work.....

Thanks for the help!

quote:
Originally posted by James K

[quote]Originally posted by jcb267

Hi -

I am trying to output data to a file but cant get this to work:

select *

into OUT C:\Temp\prem.txt

from ##Prem

SQL apparently does not like the "\" in the file path. Is there another way to do this?

pradeepbliss
Starting Member

28 Posts

Posted - 2015-02-13 : 03:59:29

if you are trying for a output file through BCP(Bulk Copy Program) :
syntax for BCP OUT: bcp DB_Name.Table_Name OUT E:\Table_Name.bat -c -S (local) -Usa -P***** -t -n
syntax for BCP IN: bcp DB_Name.TABLE_NAME IN E:\Table_Name.bat -c -S (local)-Usa -P***** -t -n
BCP will copy Thousands of rows in a few seconds...just open command prompt and execute BCP OUT and BCP IN ..

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-02-13 : 08:56:24

I dont follow this pradeepbliss, I have not used BCP before. Can you explain a little more and give me an example?

How do I start BCP? Is it in a query window?

quote:
Originally posted by pradeepbliss

if you are trying for a output file through BCP(Bulk Copy Program) :
syntax for BCP OUT: bcp DB_Name.Table_Name OUT E:\Table_Name.bat -c -S (local) -Usa -P***** -t -n
syntax for BCP IN: bcp DB_Name.TABLE_NAME IN E:\Table_Name.bat -c -S (local)-Usa -P***** -t -n
BCP will copy Thousands of rows in a few seconds...just open command prompt and execute BCP OUT and BCP IN ..

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-13 : 09:18:02

BCP is a Windows command-line program.

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-02-13 : 13:30:22

Should BCP be used for this?

quote:
Originally posted by gbritton

BCP is a Windows command-line program.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-13 : 13:44:58

Well, that's one of the main purposes of BCP.

pradeepbliss
Starting Member

28 Posts

Posted - 2015-02-16 : 08:49:50

BCP Queryout:

1)first enable XP_cmdshell in sql
Use Master
GO
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

2)execute this script in query window for default instance
EXEC xp_cmdshell 'bcp "SELECT * FROM DBName.TableName" queryout "E:\Pradeep.txt" -T -c -t "|",'
4.3)execute this script in query window for named instance
EXEC xp_cmdshell 'bcp "SELECT * FROM DBName.TableName" queryout "E:\pradeep.txt" -T -S192.168.1.136\SmartPOSR2S -c -t,"|",'

quote:
Originally posted by jcb267

I dont follow this pradeepbliss, I have not used BCP before. Can you explain a little more and give me an example?

How do I start BCP? Is it in a query window?
quote:
Originally posted by pradeepbliss

if you are trying for a output file through BCP(Bulk Copy Program) :
syntax for BCP OUT: bcp DB_Name.Table_Name OUT E:\Table_Name.bat -c -S (local) -Usa -P***** -t -n
syntax for BCP IN: bcp DB_Name.TABLE_NAME IN E:\Table_Name.bat -c -S (local)-Usa -P***** -t -n
BCP will copy Thousands of rows in a few seconds...just open command prompt and execute BCP OUT and BCP IN ..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources