如何自动将数据从SQL Server 2012导出到CSV文件? [英] How to automatically export data from SQL Server 2012 to CSV file?

问题描述

希望我不会通过问一个简单的问题而使任何人不高兴！

Hope I dont upset anybody by asking too simple a question!

我需要将数据从SQL Server 2012表导出到CSV文件.无论何时创建新记录或更新/删除现有记录，都需要每小时进行一次，或者如果可能的话，最好进行一次.该表包含我们维护的所有站点的列表.我需要将此CSV文件导出到特定位置，因为第三方数据库中有一个API可以监控此位置并从那里导入CSV文件.

I have a requirement to export data from a SQL Server 2012 table, to a CSV file. This needs to be done either every hour, or ideally if it is possible, whenever a new record is created or an existing record is updated/deleted. The table contains a list of all Sites we maintain. I need to export this CSV file to a particular location, as there is an API from a third party database which monitors this location and imports CSV files from there.

要从SQL中提取的数据是:

The data to be extracted from SQL is:

Mxmservsite.siteid as Marker_ID, mxmservsite.name as Name, 'SITE' as Group, '3' as Status, 
'' as Notes, mxmservsite.zipcode as Post_Code, 'GB' as Country, '' as Latitude, 
'' as Longitude, '' as Delete
Where dataareaid='ansa'

任何人都知道我该如何去做吗?抱歉，我是SQL的新手，仍然在学习基础知识！我过去搜索过类似的问题，但还没有发现任何东西.我知道有一个名为BCP的实用程序，但是不确定这是否是最好的方法，如果可以，那么如何使用它每小时运行一次，或者何时有记录更新/删除/插入操作?

Anyone have any clues how I can go about doing this? Sorry, I am a newbie with SQL and still learning the basics! I have searched for similar questions in the past, but havent found anything. I know there is a utility called BCP, but not sure whether that would be the best way, and if it would be, then how do I use it to run every hour, or whenever there is a record update/delete/insert?

欢呼

推荐答案

这里有一些功能强大的功能可以满足您的需求；只需使用Windows Task Scheduler计划它即可:

Here's some powershell that would do what you're after; just schedule it using the Windows Task Scheduler:

function Execute-SQLQuery {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]$DbInstance
        ,
        [Parameter(Mandatory = $true)]
        [string]$DbCatalog
        ,
        [Parameter(Mandatory = $true)]
        [string]$Query
        ,
        [Parameter(Mandatory = $false)]
        [int]$CommandTimeoutSeconds = 30 #this is the SQL default
    )
    begin {
        write-verbose "Call to 'Execute-SQLQuery': BEGIN"
        $connectionString = ("Server={0};Database={1};Integrated Security=True;" -f $DbInstance,$DbCatalog)
        $connection = New-Object System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = $connectionString
        $connection.Open()    
    }
    process {
        write-verbose "`n`n`n-----------------------------------------"
        write-verbose "Call to 'Execute-SQLQuery': PROCESS"
        write-verbose $query 
        write-verbose "-----------------------------------------`n`n`n"
        $command = $connection.CreateCommand()
        $command.CommandTimeout = $CommandTimeoutSeconds
        $command.CommandText = $query
        $result = $command.ExecuteReader()
        $table = new-object "System.Data.DataTable"
        $table.Load($result)
        Write-Output $table
    }
    end {
        write-verbose "Call to 'Execute-SQLQuery': END"
        $connection.Close()
    }
}

Execute-SQLQuery -DbInstance 'myServer\InstanceName' -DbCatalog 'myDatabase' -Query @"
select Mxmservsite.siteid as Marker_ID
 , mxmservsite.name as Name
 , 'SITE' as Group
 , '3' as Status
 , '' as Notes
 , mxmservsite.zipcode as Post_Code
 , 'GB' as Country
 , '' as Latitude
 , '' as Longitude
 , '' as Delete
 From mxmservsite --this wasn't in your original code
 Where dataareaid='ansa'
 "@ | Export-CSV '.\MyOutputFile.csv' -NoType 

有可能在任何更改时触发某些事情；也就是说，您可以在表上创建触发器，然后使用 xp_cmdshell 执行脚本或类似内容；但这会导致性能问题(如果在未完全理解的情况下使用触发器，通常会是一个不好的选择).此外，xp_cmdshell还使您面临一些安全风险.

To have something triggered on any change is possible; i.e. you could create a trigger on the table, then use xp_cmdshell to execute a script or similar; but that's going to lead to performance problems (triggers are often a bad option if used without being fully understood). Also xp_cmdshell opens you up to some security risks.

还有许多其他方法可以实现这一目标；目前，我对PowerShell有一个需求，因为它可以为您带来大量的灵活性，而却没有太大的开销.

There are many other ways to achieve this; currently I have a thing for PowerShell as it gives you loads of flexibility with little overhead.

另一种选择可能是使用 linked servers 允许您的源数据库直接更新目标，而无需CSV.

Another option may be to look into using linked servers to allow your source database to directly update the target without need for CSV.

这篇关于如何自动将数据从SQL Server 2012导出到CSV文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！