导出数据库中表数据为CSV文件(带表头)

最新推荐文章于 2022-12-27 10:31:35 发布

原创最新推荐文章于 2022-12-27 10:31:35 发布 · 9.4k 阅读

2 ·

CC 4.0 BY-SA版权

SQL Server 专栏收录该内容

14 篇文章

订阅专栏

该博客介绍了如何通过执行SQLCMD命令，结合xp_cmdshell扩展存储过程，将数据库中的表数据导出为包含表头的CSV文件。具体操作演示了从IPVA_Builder库的Build_GetCsvResLog表中提取所有数据，并保存到E盘的123文件夹下，生成名为Build_GetCsvResLog.csv的文件。

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