将值从 SQL Server 导出到 txt 文件 [英] Export values from SQL Server to txt file

问题描述

我想将值从 SQL Server 导出到 txt 文件.我知道 BCP，在那里我需要提供查询或表名才能从表中导出数据.我不想导出数据，但我想导出分配给变量的值.我该怎么做，有什么帮助吗?

I want to export values from SQL Server to txt file. I know about BCP, there I need to give either query or table name to export data from a table. I don't want to export data but I want to export values that are assigned to variable. How can I do that, any help?

推荐答案

使用查询来收集要导出的变量.像这样:

Use a query to collect the variables you want to export. Something like this:

DECLARE @var1 INTEGER
DECLARE @var2 INTEGER

SELECT @var1 = 10
SELECT @var2 = 22

SELECT 'variable 1' AS VarName, @var1 AS VarValue
UNION
SELECT 'variable 2' AS VarName, @var2 AS VarValue

在以下命令中使用此查询语句.使用 queryout 并将 [querystatement] 替换为上面的语句，或者使用一个变量作为查询字符串.

Use this query statement in the following command. Use queryout and replace [querystatement] with the statement above, or use a variable for the query string.

EXEC master..XP_CMDSHELL 'bcp "[querystatement]" queryout "c:\spt_values.dat"'

如果变量需要在语句外声明:

If the variable needs to be declared outside the statement:

DECLARE @cmd varchar(1000)
DECLARE @sql varchar(8000) 
DECLARE @var1 int
SELECT @var1 = 10
SET @cmd='"select '+CAST(@var1 AS VARCHAR(10))+'"' 
SELECT @sql = 'bcp '+@cmd+' queryout I:\File\mytest.txt -c -t -T -S YAMUNA\SQLEXPRESS';
exec xp_cmdshell @sql;

这篇关于将值从 SQL Server 导出到 txt 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！