SQL Server存储过程以将“选择结果"导出到CSV [英] SQL Server stored procedure to export Select Result to CSV

问题描述

在存储过程中，我想将选择结果导出到 .CSV 文件中.我需要编写一个存储过程，该存储过程从不同的表中选择一些数据并将其保存到 .CSV 文件中.

In my stored procedure, I want to export select result to a .CSV file. I need to write a stored procedure which selects some data from different tables and saves it to a .CSV file.

选择零件已准备就绪

 SELECT DISTINCT 
                  PER.NREGNUMBER_PERNUM AS [Registration Number], 
                  PER.CFAMNAME_PER AS [Family Name], 
                  PER.CGIVNAME_PER AS [Given Name], 
                  CONVERT(varchar(10), CONVERT(date, PER.DBIRTHDATE_PER, 106), 103) AS [Birth Date], 
                  PER.CGENDER_PERGEN as [Gender],
                  PHONE.MOBILE_NUMBER
        FROM      PERSON AS PER 
                  LEFT OUTER JOIN
                  PHONE ON PER.NREGNUMBER_PERNUM = PHONE.NREGNUMBER_PPHPER AND PHONE.CPRIMARY_PPH = 'Y'

推荐答案

任务是我必须在指定的时间从数据库导出一些数据到 .CSV .首先，我们要使用Windows Scheduler来运行stp.STP必须能够导出数据.但是我找不到办法.相反，我们所做的就是创建仅带来数据的简单STP.然后，我们创建了批处理文件，该文件调用STP并将结果导出到 .CSV 文件.批处理文件很简单

The task was I had to export from database some data to .CSV at specified time. In the begining we wanted to use windows scheduler for running stp. The STP had to be able to export data. But I couldn't find a way. Instead the thing what we did was creating simple STP which brings only data . And we created batch file which calls STP and export result to .CSV file. The batch file is simple

sqlcmd -S Etibar-PC\SQLEXPRESS -d MEV_WORK -E -Q "dbo.SelectPeople" -o "MyData1.csv" -h-1 -s"," -w 700

dbo.SelectPeople是STP
Etibar-PC \ SQLEXPRESS是架构
MEV_WORK是数据库名称.

dbo.SelectPeople is STP
Etibar-PC\SQLEXPRESS is Schema
MEV_WORK is Database name.

这篇关于SQL Server存储过程以将“选择结果"导出到CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！