数据导出

只举几个例子，防止工作中用到找不着

 

 

1、存储过程

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author: <j>

-- Create date: <2013-3-15>

-- Description: <每天从CrbtInfo表中导出前一天更新的彩铃信息>

-- =============================================

ALTER PROCEDURE [dbo].[Job_ExportCrbtInfo]

@servername varchar(64),

@username varchar(32),

@password varchar(32),

@filepath varchar(128)

AS

BEGIN

DECLARE @FileName varchar(128)

--文件名：CrbtInfo_2013-03-15.txt (生成的是前一天的日期)

SET @FileName = @filepath + 'CrbtInfo_' + Convert(varchar(10), Dateadd(day, -1, GETDATE()), 23) + '.txt'

 

DECLARE @QuerySQL varchar(1024), @ExportSQL varchar(2048)

--前一天更新的数据

SET @QuerySQL = 'SELECT CASE EventType WHEN ''1'' THEN ''1'' WHEN ''2'' THEN ''3'' WHEN ''5'' THEN ''3'' WHEN ''3'' THEN ''2'' WHEN ''4'' THEN ''2'' WHEN ''6'' THEN ''2'' END AS EventType, ToneID, ToneName, CmsSingerName, SpName, Price, ToneValidDay, PlayFilePath, AuditFilePath, Type, ''0'' AS Status FROM BaseResource.dbo.CrbtInfo WHERE Convert(varchar(10), UploadedTime, 23) = Convert(varchar(10), Dateadd(day, -1, GETDATE()), 23)'

PRINT @QuerySQL

 

SET @ExportSQL = 'BCP "' + @QuerySQL + '" queryout ' + @FileName + ' -c -q -t"|" -S"' + @servername + '" -U"' + @username + '" -P"' + @password + '" '

PRINT @ExportSQL

 

EXEC master..xp_cmdshell @ExportSQL

 

END

 

2、执行存储过程

USE [DBName]

GO

 

DECLARE@return_value int

 

EXEC@return_value = [dbo].[Job_ExportCrbtInfo]

@servername = N'',

@username = N'',

@password = N'',

@filepath = N''

 

SELECT'Return Value' = @return_value

 

GO

 

2、有的需要更改配置后才能执行

-- 允许配置高级选项

EXEC sp_configure 'show advanced options', 1

GO

-- 重新配置

RECONFIGURE

GO

-- 启用xp_cmdshell

EXEC sp_configure 'xp_cmdshell', 1

GO

--重新配置

RECONFIGURE

GO

 

DECLARE @SqlALL VARCHAR(8000)

DECLARE @FILEPATH VARCHAR(80)

set @FILEPATH='e:/'

SET @SqlALL = 'bcp "SELECT ISNULL(UserNoAndPersonalRadioNo.NickName,'''') ,[RadioFileName],UserRadioInfo.CreatedTime,UserRadioInfo.ReplyNo FROM MusicSearching40.dbo.[UserRadioInfo] LEFT JOIN MusicSearching40.dbo.UserNoAndPersonalRadioNo ON UserRadioInfo.UserNo = UserNoAndPersonalRadioNo.UserNo WHERE (Status = ''1'' OR Status = ''4'') and UserNoAndPersonalRadioNo.RadioType=''1'' and len(UserRadioInfo.ReplyNo)>0 and CreatedTime>(getdate()-1) ORDER BY UserNoAndPersonalRadioNo.NickName DESC,CreatedTime DESC" queryout '+@FILEPATH+'留言文件_'+CONVERT(varchar(10),GETDATE(),112)+'.txt -c -t"\t" -T -U''sa'' -P''ms40@dev'' '

EXEC master..xp_cmdshell @SqlALL

 

3、作业 可以在sqlserver中建个作业，定时进行数据导出等工作

 

 注意：在执行sql时有时会遇到下面的问题：

output

SQLState = S1000, NativeError = 0

Error = [Microsoft][SQL Native Client]无法打开 BCP 主数据文件

NULL

这个问题是因为导出文件的路径不存在或着不正确导致的