EXEC xp_cmdshell 'sqlcmd -E -Q "select * from IPVA_Builder..Build_GetCsvResLog" -o E:\123\Build_GetCsvResLog.csv '
/****** Object: StoredProcedure [dbo].[user_ExportDataCSV] Script Date: 06/18/2017 17:48:09 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[user_ExportDataCSV]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[user_ExportDataCSV]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[user_ExportDataCSV]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: tanxh
-- Create date: 2017-06-18
-- Description: 导出库中所有数据为csv文件
-- EXEC [dbo].[user_ExportDataCSV] ''E:\123\''
-- =============================================
CREATE PROCEDURE [dbo].[user_ExportDataCSV]
@path nvarchar(1000)
AS
BEGIN
declare @DBName nvarchar(100)
declare @SQL varchar(2000)
declare @n int,@count int
declare @TableName nvarchar(100)
create table #TableName
(
ID INT IDENTITY(1,1),
Name nvarchar(100)
)
insert into #TableName
select name from sysobjects where xtype=''u''
select @count=COUNT(*) from sysobjects where xtype=''u''
select @DBName=DB_NAME()
set @n=1
while(@n<=@count)
begin
select @TableName=Name from #TableName where ID=@n
set @SQL=''sqlcmd -E -Q "select * from ''+@DBName+''..''+@TableName+''" -o ''+@path+''''+@TableName+''.csv ''
exec xp_cmdshell @SQL
set @n=@n+1
end
END
'
END
GO
该博客介绍了如何通过执行SQLCMD命令,结合xp_cmdshell扩展存储过程,将数据库中的表数据导出为包含表头的CSV文件。具体操作演示了从IPVA_Builder库的Build_GetCsvResLog表中提取所有数据,并保存到E盘的123文件夹下,生成名为Build_GetCsvResLog.csv的文件。
1564

被折叠的 条评论
为什么被折叠?



