27,579
社区成员
发帖
与我相关
我的任务
分享
bcp "exec tempdb.dbo.Proc_QueryWithHeader" queryout "D:\1.csv"
我这样写报错 需要参数@tablenameUSE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
n NVARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT INTO t VALUES ('a'),('bc'),('小明')
GO
IF OBJECT_ID('dbo.Proc_QueryWithHeader') IS NOT NULL
DROP PROC dbo.Proc_QueryWithHeader
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE dbo.Proc_QueryWithHeader
@tableName NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT '+STUFF(
(
select ',CAST('''+c.name+''' AS nvarchar(max)) as ['+c.name+']' from
sys.columns AS c WHERE c.[object_id]=OBJECT_ID(@tableName)
FOR XML PATH('')
),1,1,'')
SET @sql= @sql+ 'union all SELECT ' + STUFF(
(
SELECT ',CAST(['+c.name+'] as nvarchar(max)) AS ['+c.name+']'
FROM sys.[columns] AS c WHERE c.[object_id]=OBJECT_ID(@tableName)
FOR XML PATH('')),1,1,''
)
+' FROM ['+@tableName+']'
PRINT @sql
EXEC (@sql)
END
GO
EXEC Proc_QueryWithHeader 't'
/*
SELECT CAST('id' AS NVARCHAR(MAX)) AS [id],
CAST('n' AS NVARCHAR(MAX)) AS [n]
UNION ALL
SELECT CAST([id] AS NVARCHAR(MAX)) AS [id],
CAST([n] AS NVARCHAR(MAX)) AS [n]
FROM [t]
*/
/*
id n
----------
id n
1 a
2 bc
3 小明
*/
这个存储过程, 可以得到第一行包含表头的查询结果, 你试下吧。select Name From syscolumns where id=OBJECT_ID('TableZp1')
可以查出列名,bcpbcp "select * FROM test02.dbo.TableZp1" queryout "D:\1.csv" -c -t, -S "xxxx" -U xx -Pxxx
可以导出无表头数据,我想把他们合起来,写到一个.bat文件中或.sh文件中