blocks|key|1708160|text|这是一个导出到Excel的选项I+found+(我相信可以针对CSV进行修改)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1708161|insert+into+OPENROWSET('Microsoft.Jet.OLEDB.4.0',+
'Excel+8.0;Database=D:\testing.xls;',+
'SELECT+*+FROM+[SheetName$]')+select+*+from+SQLServerTable|code-block|syntax|javascript|1708162|entityMap|0|LINK|mutability|MUTABLE|url|http://beyondrelational.com/modules/2/blogs/70/posts/10792/importexport-to-excel.aspx^0|F|7|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Here is an option <a href="http://beyondrelational.com/modules/2/blogs/70/posts/10792/importexport-to-excel.aspx">I found</a> to export to Excel (can be modified for CSV I believe)

<pre><code>insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=D:\testing.xls;', 
'SELECT * FROM [SheetName$]') select * from SQLServerTable
</code></pre>

blocks|key|1708258|text|您也可以使用以下Node.js模块轻松完成此操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1708259|https://www.npmjs.com/package/mssql-to-csv|offset|length|1708260|var+mssqlExport+=+require('mssql-to-csv')

++++//+All+config+options+supported+by+https://www.npmjs.com/package/mssql+
++++var+dbconfig+=+{
++++++++user:+'username',
++++++++password:+'pass',
++++++++server:+'servername',
++++++++database:+'dbname',
++++++++requestTimeout:+320000,
++++++++pool:+{
++++++++++++max:+20,
++++++++++++min:+12,
++++++++++++idleTimeoutMillis:+30000
++++++++}
++++};

++++var+options+=+{
++++++++ignoreList:+["sysdiagrams"],+//+tables+to+ignore+
++++++++tables:+[],++++++++++++++++++//+empty+to+export+all+the+tables+
++++++++outputDirectory:+'somedir',
++++++++log:+true
++++};

++++mssqlExport(dbconfig,+options).then(function(){
++++++++console.log("All+done+successfully!");
++++++++process.exit(0);
++++}).catch(function(err){
++++++++console.log(err.toString());
++++++++process.exit(-1);
+++});|code-block|syntax|javascript|1708261|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|16|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@$D|T|E|U|1|V]]|A|$]]|$1|F|3|G|5|H|7|W|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|C]]]]

You can also use following Node.js module to do it with ease:

<a href="https://www.npmjs.com/package/mssql-to-csv" rel="noreferrer">https://www.npmjs.com/package/mssql-to-csv</a>

<pre><code>var mssqlExport = require('mssql-to-csv')

 // All config options supported by https://www.npmjs.com/package/mssql 
 var dbconfig = {
 user: 'username',
 password: 'pass',
 server: 'servername',
 database: 'dbname',
 requestTimeout: 320000,
 pool: {
 max: 20,
 min: 12,
 idleTimeoutMillis: 30000
 }
 };

 var options = {
 ignoreList: ["sysdiagrams"], // tables to ignore 
 tables: [], // empty to export all the tables 
 outputDirectory: 'somedir',
 log: true
 };

 mssqlExport(dbconfig, options).then(function(){
 console.log("All done successfully!");
 process.exit(0);
 }).catch(function(err){
 console.log(err.toString());
 process.exit(-1);
 });
</code></pre>

blocks|key|1921726|text|rsubmit;
options+missing=0;
ods+listing+close;
ods+csv+file='\\FILE_PATH_and_Name_of_report.csv';

proc+sql;
SELECT+*
FROM+`YOUR_FINAL_TABLE_NAME';
quit;
ods+csv+close;

endrsubmit;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1921727|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>rsubmit;
options missing=0;
ods listing close;
ods csv file='\\FILE_PATH_and_Name_of_report.csv';

proc sql;
SELECT *
FROM `YOUR_FINAL_TABLE_NAME';
quit;
ods csv close;

endrsubmit;
</code></pre>

blocks|key|1708386|text|对于SSMS或编程语言，您不需要经历繁琐的过程。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1708387|以管理员身份运行PowerShell提示符，然后：|1708388|Install-Module+-Name+SqlServer|code-block|syntax|javascript|1708389|安装后，您可以将任何表导出到CSV，如下所示：|1708390|Invoke-Sqlcmd+-Query+"SELECT+*+FROM+[mydatabase].[dbo].[mytable]"+-ServerInstance+"MYSQLSERVER"+%7C+Export-Csv+-Path+"c:\temp\output.csv"+-NoTypeInformation|1708391|您可以将其放在.PS1脚本中，并随心所欲地执行它。|1708392|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

You don't need to go through hoops with SSMS or programming languages.
Run a PowerShell prompt as administrator, then:
<pre><code>Install-Module -Name SqlServer
</code></pre>
Once installed you can export any table to CSV like this:
<pre><code>Invoke-Sqlcmd -Query &quot;SELECT * FROM [mydatabase].[dbo].[mytable]&quot; -ServerInstance &quot;MYSQLSERVER&quot; | Export-Csv -Path &quot;c:\temp\output.csv&quot; -NoTypeInformation
</code></pre>
You can put that in a .PS1 script and execute it however you like.

blocks|key|1921746|text|我写了一个小工具就能做到这一点。代码可以在on+github上找到。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1921747|要将一个(或多个)+SQL查询的结果转储到一个(或多个)+CSV文件，请执行以下操作：|1921748|java+-jar+sql_dumper.jar+/path/sql/files/+/path/out/+user+pass+jdbcString|code-block|syntax|javascript|1921749|干杯。|1921750|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/BernhardSchlegel/SQL-dumper^0|L|9|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|Z|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|C|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

I wrote a small tool that does just that. Code is available <a href="https://github.com/BernhardSchlegel/SQL-dumper" rel="nofollow noreferrer">on github</a>.

To dump the results of one (or more) SQL queries to one (or more) CSV files:

<pre><code>java -jar sql_dumper.jar /path/sql/files/ /path/out/ user pass jdbcString
</code></pre>

Cheers.

blocks|key|1708360|text|当你出于某种原因想要所有的表时呢？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1708361|您可以在SSMS中生成以下命令：|1708362|SELECT+
CONCAT('sqlcmd+-S+',
'Your(local?)SERVERhere'
,'+-d',
'YourDB'
,'+-E+-s,+-W+-Q+"SELECT+*+FROM+',
TABLE_NAME,
'"+>',
TABLE_NAME,
'.csv')+FROM+INFORMATION_SCHEMA.TABLES|code-block|syntax|javascript|1708363|然后再次得到像这样的行|1708364|sqlcmd+-S+...+-d...+-E+-s,+-W+-Q+"SELECT+*+FROM+table1"+>table1.csv
sqlcmd+-S+...+-d...+-E+-s,+-W+-Q+"SELECT+*+FROM+table2"+>table2.csv
...|1708365|也可以选择使用更好的制表符作为分隔符，但它需要一个奇怪的Unicode字符-在CMD中使用Alt%2B9，它类似于这个0+(Unicode+CB25)，但只能通过复制/粘贴到命令行来工作，而不是批处理。|1708366|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

And when you want all tables for some reason ?

You can generate these commands in SSMS:

<pre><code>SELECT 
CONCAT('sqlcmd -S ',
'Your(local?)SERVERhere'
,' -d',
'YourDB'
,' -E -s, -W -Q "SELECT * FROM ',
TABLE_NAME,
'" &gt;',
TABLE_NAME,
'.csv') FROM INFORMATION_SCHEMA.TABLES
</code></pre>

And get again rows like this

<pre><code>sqlcmd -S ... -d... -E -s, -W -Q "SELECT * FROM table1" &gt;table1.csv
sqlcmd -S ... -d... -E -s, -W -Q "SELECT * FROM table2" &gt;table2.csv
...
</code></pre>

There is also option to use better TAB as delimiter, but it would need a strange Unicode character - using Alt+9 in CMD, it came like this ○ (Unicode CB25), but works only by copy/paste to command line not in batch.

blocks|key|1708353|text|也许是死马，但不久前我也在尝试做同样的事情，我遇到了一个脚本来创建一个STP，它试图做我想要做的事情，但它有一些奇怪的地方需要注意。为了找出我在哪里找到了发布更新的脚本，我偶然发现了这个帖子，它似乎是一个分享它的好地方。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1708354|这个STP+(在很大程度上我不相信它，而且我找不到我在其上找到它的站点)，它接受模式名称、表名和Y或N，以包括或排除头部作为输入参数，并查询所提供的表，以逗号分隔、带引号的csv格式输出每一行。|1708355|我已经对原始脚本做了许多修复/更改，但它的骨架都来自OP，不管是谁。|1708356|下面是脚本：|1708357|IF+OBJECT_ID('get_csvFormat',+'P')+IS+NOT+NULL
++++DROP+PROCEDURE+get_csvFormat
GO

CREATE+PROCEDURE+get_csvFormat(@schemaname+VARCHAR(20),+@tablename+VARCHAR(30),@header+char(1))
AS
BEGIN
++++IF+ISNULL(@tablename,+'')+=+''
++++BEGIN
++++++++PRINT('NO+TABLE+NAME+SUPPLIED,+UNABLE+TO+CONTINUE')
++++++++RETURN
++++END
++++ELSE
++++BEGIN
++++++++DECLARE+@cols+VARCHAR(MAX),+@sqlstrs+VARCHAR(MAX),+@heading+VARCHAR(MAX),+@schemaid+int

++++++++--if+no+schemaname+provided,+default+to+dbo
++++++++IF+ISNULL(@schemaname,+'')+=+''
++++++++++++SELECT+@schemaname+=+'dbo'

++++++++--if+no+header+provided,+default+to+Y
++++++++IF+ISNULL(@header,+'')+=+''
++++++++++++SELECT+@header+=+'Y'

++++++++SELECT+@schemaid+=+(SELECT+schema_id+FROM+sys.schemas+WHERE+[name]+=+@schemaname)
++++++++SELECT+
++++++++@cols+=+(
++++++++++++SELECT+'+,+CAST([',+b.name+%2B+']+AS+VARCHAR(50))+'++
++++++++++++FROM+sys.objects+a+
++++++++++++INNER+JOIN+sys.columns+b+ON+a.object_id=b.object_id+
++++++++++++WHERE+a.name+=+@tablename+AND+a.schema_id+=+@schemaid
++++++++++++FOR+XML+PATH('')
++++++++),
++++++++@heading+=+(
++++++++++++SELECT+',"'+%2B+b.name+%2B+'"'+FROM+sys.objects+a+
++++++++++++INNER+JOIN+sys.columns+b+ON+a.object_id=b.object_id+
++++++++++++WHERE+a.name=+@tablename+AND+a.schema_id+=+@schemaid
++++++++++++FOR+XML+PATH('')
++++++++)

++++++++SET+@tablename+=+@schemaname+%2B+'.'+%2B+@tablename
++++++++SET+@heading+=++'SELECT+'''+%2B+right(@heading,len(@heading)-1)+%2B+'''+AS+CSV,+0+AS+Sort'++%2B+CHAR(13)
++++++++SET+@cols+=++'''"'','+%2B+replace(right(@cols,len(@cols)-1),',',+',''","'',')+%2B+',''"'''+%2B+CHAR(13)

++++++++IF+@header+=+'Y'
++++++++++++SET+@sqlstrs+=++'SELECT+CSV+FROM+('+%2B+CHAR(13)+%2B+@heading+%2B+'+UNION+SELECT+CONCAT('+%2B+@cols+%2B+')+CSV,+1+AS+Sort+FROM+'+%2B+@tablename+%2B+CHAR(13)+%2B+')+X+ORDER+BY+Sort,+CSV+ASC'
++++++++ELSE
++++++++++++SET+@sqlstrs+=++'SELECT+CONCAT('+%2B+@cols+%2B+')+CSV+FROM+'+%2B+@tablename+

++++++++IF+@schemaid+IS+NOT+NULL+
++++++++++++EXEC(@sqlstrs)
++++++++ELSE+
++++++++++++PRINT('SCHEMA+DOES+NOT+EXIST')
++++END
END

GO

--------------------------------------

--EXEC+get_csvFormat+@schemaname='dbo',+@tablename='TradeUnion',+@header='Y'|code-block|syntax|javascript|1708358|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|R|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Dead horse perhaps, but a while back I was trying to do the same and came across a script to create a STP that tried to do what I was looking for, but it had a few quirks that needed some attention. In an attempt to track down where I found the script to post an update, I came across this thread and it seemed like a good spot to share it.

This STP (Which for the most part I take no credit for, and I can't find the site I found it on), takes a schema name, table name, and Y or N [to include or exclude headers] as input parameters and queries the supplied table, outputting each row in comma-separated, quoted, csv format. 

I've made numerous fixes/changes to the original script, but the bones of it are from the OP, whoever that was.

Here is the script:

<pre><code>IF OBJECT_ID('get_csvFormat', 'P') IS NOT NULL
 DROP PROCEDURE get_csvFormat
GO

CREATE PROCEDURE get_csvFormat(@schemaname VARCHAR(20), @tablename VARCHAR(30),@header char(1))
AS
BEGIN
 IF ISNULL(@tablename, '') = ''
 BEGIN
 PRINT('NO TABLE NAME SUPPLIED, UNABLE TO CONTINUE')
 RETURN
 END
 ELSE
 BEGIN
 DECLARE @cols VARCHAR(MAX), @sqlstrs VARCHAR(MAX), @heading VARCHAR(MAX), @schemaid int

 --if no schemaname provided, default to dbo
 IF ISNULL(@schemaname, '') = ''
 SELECT @schemaname = 'dbo'

 --if no header provided, default to Y
 IF ISNULL(@header, '') = ''
 SELECT @header = 'Y'

 SELECT @schemaid = (SELECT schema_id FROM sys.schemas WHERE [name] = @schemaname)
 SELECT 
 @cols = (
 SELECT ' , CAST([', b.name + '] AS VARCHAR(50)) ' 
 FROM sys.objects a 
 INNER JOIN sys.columns b ON a.object_id=b.object_id 
 WHERE a.name = @tablename AND a.schema_id = @schemaid
 FOR XML PATH('')
 ),
 @heading = (
 SELECT ',"' + b.name + '"' FROM sys.objects a 
 INNER JOIN sys.columns b ON a.object_id=b.object_id 
 WHERE a.name= @tablename AND a.schema_id = @schemaid
 FOR XML PATH('')
 )

 SET @tablename = @schemaname + '.' + @tablename
 SET @heading = 'SELECT ''' + right(@heading,len(@heading)-1) + ''' AS CSV, 0 AS Sort' + CHAR(13)
 SET @cols = '''"'',' + replace(right(@cols,len(@cols)-1),',', ',''","'',') + ',''"''' + CHAR(13)

 IF @header = 'Y'
 SET @sqlstrs = 'SELECT CSV FROM (' + CHAR(13) + @heading + ' UNION SELECT CONCAT(' + @cols + ') CSV, 1 AS Sort FROM ' + @tablename + CHAR(13) + ') X ORDER BY Sort, CSV ASC'
 ELSE
 SET @sqlstrs = 'SELECT CONCAT(' + @cols + ') CSV FROM ' + @tablename 

 IF @schemaid IS NOT NULL 
 EXEC(@sqlstrs)
 ELSE 
 PRINT('SCHEMA DOES NOT EXIST')
 END
END

GO

--------------------------------------

--EXEC get_csvFormat @schemaname='dbo', @tablename='TradeUnion', @header='Y'
</code></pre>

I want to:
Export table from sql server database to a comma delimited <code>csv</code> file without using sql Server import export wizard

I want to do it using a query because I want to use the query in automation

Is it possible? I searched for that and didn't find a good answer

Export table from database to csv file

数据库

我希望:在不使用sql server导入导出向导的情况下，将表从sql Server数据库导出到逗号分隔的csv文件。我想使用查询来完成，因为我想在自动化中使用查询有可能吗？我找了一下，没有找到一个好的答案

问将表从数据库导出到csv文件
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将表从数据库导出到csv文件EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将表从数据库导出到csv文件
EN