需要帮助将表从sql server 2008导出到文本文件 [英] need help to export table from sql server 2008 to text file

问题描述

我正在尝试将ms sql server 2008中存在的表导出到系统上的文本文件. 我正在sql server查询窗口中编写以下命令

I am trying to export a table present in ms sql server 2008 to a text file on my system. I am writing the following command in sql server query window

SELECT *
FROM [AdventureWorks].[Person].[AddressType] 
INTO OUTFILE 'C:/filename.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

现在，每当我编写此命令时，sql帮助就会给我incorrect syntax near 'INTO'

Now whenever I write this command the sql help gives me error that incorrect syntax near 'INTO'

然后我尝试按以下方式从关键字和关键字之间进行互换

then I tried interchanging from and into keywords as follows

SELECT *
INTO OUTFILE 'C:/filename.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM [AdventureWorks].[Person].[AddressType] ;

现在它给了我incorrect syntax near 'C:/filename.csv'

请帮助我. 我无法删除这些错误并无法正常运行SQL

Please help me regarding this. I am not able to remove these error and get a working sql

推荐答案

解决问题的方法不止一种，在这种情况下，这里有两种解决方法

There are more than many ways to solve a problem , In this case here are two solutions

解决方案1 ​​

• 右键单击数据库名称->任务->导出数据
• 选择表作为数据源
• 选择平面文件目标作为目标
• 选择文件名(任何文件名)
• 在第一个数据行中标记列名称"(这是可选的)

就是这样.

解决方案2

DECLARE  
 @saveas VARCHAR(2048)
,@query VARCHAR(2048)
,@bcpquery VARCHAR(2048)
,@bcpconn VARCHAR(64)
,@bcpdelim VARCHAR(2)

 SET @query      = 'select * from table1'
 SET @saveas     = '\\SERVER1\SHARE1\FOLDER\QueryOutput.txt'
 SET @bcpdelim   = '|'
 SET @bcpconn    = '-T' -- Trusted
 --SET @bcpconn    = '-U <username> -P <password>' -- SQL authentication


 SET @bcpquery = 'bcp "' + replace(@query, char(10), '') + '" QUERYOUT "' + @saveas + '" -c -t^' + @bcpdelim + ' ' + @bcpconn + ' -S ' + @@servername
EXEC master..xp_cmdshell @bcpquery  

这篇关于需要帮助将表从sql server 2008导出到文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！