在SQL Server中使用输出值 [英] Use output values in SQL server
本文介绍了在SQL Server中使用输出值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在sql server中,我从两个表中得到不同的记录,如试图显示
i想在sql server中使用输出值?
我尝试过:
SELECT derivedtable.NewColumn
FROM
(
选择CustomerAccountID作为NewColumn FROM [dbo]。[Order]其中ShipmentStatusID = 4,EndDate> ='2018-08-01'和EndDate< ='2018-08-07'
UNION
SELECT CustomerAccountID为NewColumn FROM [dbo]。[Order]其中ShipmentStatusID = 5,EndDate> ='2018-08-01'和EndDate< ='2018-08-07'
union
SELECT CustomerAccountID as NewColumn FROM [dbo]。[OtherCharges]其中EntryDate> ='2018-08-01'和EntryDate< ='2018-08-07'
)derivedtable
WHERE derivedtable.NewColumn IS NOT NULL
按NewColumn订购
它显示35条记录作为输出
I想在sql server中使用这些输出值我如何使用这些值
解决方案
当然,
EXECUTE xp_cmdshell条件下的标准BCP看起来与此类似:
EXECUTE xp_cmdshell'bcpSELECT derivedtable.NewColumn(etc)... FROM [dbo]。[OtherCharges] ...(etc)... NewColumn命令queryout C:\ usersrs \mtufail \\ \\ _resultsQuery.txt -c -T -S(SERVERNAME)其中(SERVERNAME)是您的MSSQL
INSTANCENAME。这被用作连接字符串(请参阅帮助文件以获取确切的语法/语义)。
- - 您可以创建一个临时表并在该表中插入输出数据以在sql server中使用
- 例如,请参见下面的代码
IF OBJECT_ID (N ' tempdb ..#Order',N ' U') IS NOT NULL DROP TABLE #Order;
IF OBJECT_ID (N ' tempdb ..#OtherCharges',N ' U') IS NOT NULL DROP TABLE #OtherCharges;
创建 表 #Order(
CustomerAccountID varchar ( 10 ),ShipmentStatusID int , EndDate date
);
创建 表 #OtherCharges(
CustomerAccountID varchar ( 10 ),费用数字( 14 , 2 ),EntryDate date
) ;
插入 进入 #Order(CustomerAccountID ,ShipmentStatusID,EndDate)
values
(' C0001', 1 ,' 2018-02-02'),
(' C0001' , 4 ,' 2018-09-02'),
(' C0002', 1 ,' 2018-05-01'),
(< span class =code-string>' C0002', 2 ,' 2018-09-02'),
(' C0001', 5 ,' 2018-12-02'),
(' C0003', 4 ,' < span class =code-string> 2018-08-05'),
(' C0002', 4 ,' 2018 -12-30' 跨度>);
插入 进入 #OtherCharges(CustomerAccountID,Charges,EntryDate )
值
(' C0001', 1 ,' 2018- 02-02'),
(' C0001', 5000 ,' 2018-08-02'),
(' C0002', 4500 ,' 2018-05-01'),
(' C0002', 6900 ,' 2018-09-02'),
(' C0001', 7000 ,' 2018-12-02'),
(' C0003', 12000 ,' 2018-06-02'),
(' C0002', 8500 ,' 二零一八年十二月三十日' 跨度>);
DECLARE @ UpdateLog table (NewColumn varchar ( 10 ) NOT NULL );
插入 进入 @ UpdateLog
SELECT derivedtable.NewColumn
FROM
(
SELECT CustomerAccountID as NewColumn
FROM #Order
其中 ShipmentStatusID = 4 和 EndDate> = ' 2018-08-01' 和 EndDate< = ' 2018-08-07'
UNION
SELECT CustomerAccountID as NewColumn FROM #Order
其中 ShipmentStatusID = 5 和 EndDate> = ' 2018-08-01' 和 EndDate< = ' 2018-08-07'
union
SELECT CustomerAccountID as NewColumn
FROM #OtherCharges 其中 EntryDate> = ' 2018-08-01' < span class =code-keyword>和 EntryDate< = ' 2018-08-07'
)derivedtable
WHERE derivedtable.NewColumn IS NOT NULL
< span class =code-keyword> order by NewColumn
选择 * 来自 @ UpdateLog ;
In sql server i get distinct record from two table as show in tried
i want to use output values in sql server?
What I have tried:
SELECT derivedtable.NewColumn FROM ( SELECT CustomerAccountID as NewColumn FROM [dbo].[Order] where ShipmentStatusID=4 and EndDate>='2018-08-01' and EndDate<='2018-08-07' UNION SELECT CustomerAccountID as NewColumn FROM [dbo].[Order] where ShipmentStatusID=5 and EndDate>='2018-08-01' and EndDate<='2018-08-07' union SELECT CustomerAccountID as NewColumn FROM [dbo].[OtherCharges] where EntryDate>='2018-08-01' and EntryDate<='2018-08-07' ) derivedtable WHERE derivedtable.NewColumn IS NOT NULL order by NewColumn
it shows 35 record as output
I want to use these output values in sql server how can i use these values
解决方案
Sure,
The standard BCP under EXECUTE xp_cmdshell conditions looks something similar to this:
EXECUTE xp_cmdshell 'bcp "SELECT derivedtable.NewColumn (etc) ... FROM [dbo].[OtherCharges] ... (etc) ... order by NewColumn" queryout C:\users\mtufail\resultsQuery.txt -c -T -S "(SERVERNAME)"Where (SERVERNAME) is your MSSQL
INSTANCENAME. This is being used as the connection string (see the help file for exact syntax/semantics).
-- You can create a temporary table and insert your output data in that table to use in sql server -- For exmple see below code IF OBJECT_ID(N'tempdb..#Order', N'U') IS NOT NULL DROP TABLE #Order; IF OBJECT_ID(N'tempdb..#OtherCharges', N'U') IS NOT NULL DROP TABLE #OtherCharges; create table #Order ( CustomerAccountID varchar (10),ShipmentStatusID int ,EndDate date ); create table #OtherCharges ( CustomerAccountID varchar (10),Charges numeric(14,2) ,EntryDate date ); insert into #Order (CustomerAccountID,ShipmentStatusID,EndDate) values ('C0001',1,'2018-02-02'), ('C0001',4,'2018-09-02'), ('C0002',1,'2018-05-01'), ('C0002',2,'2018-09-02'), ('C0001',5,'2018-12-02'), ('C0003',4,'2018-08-05'), ('C0002',4,'2018-12-30'); insert into #OtherCharges (CustomerAccountID,Charges,EntryDate) values ('C0001',1,'2018-02-02'), ('C0001',5000,'2018-08-02'), ('C0002',4500,'2018-05-01'), ('C0002',6900,'2018-09-02'), ('C0001',7000,'2018-12-02'), ('C0003',12000,'2018-06-02'), ('C0002',8500,'2018-12-30'); DECLARE @UpdateLog table(NewColumn varchar (10) NOT NULL); insert into @UpdateLog SELECT derivedtable.NewColumn FROM ( SELECT CustomerAccountID as NewColumn FROM #Order where ShipmentStatusID=4 and EndDate>='2018-08-01' and EndDate<='2018-08-07' UNION SELECT CustomerAccountID as NewColumn FROM #Order where ShipmentStatusID=5 and EndDate>='2018-08-01' and EndDate<='2018-08-07' union SELECT CustomerAccountID as NewColumn FROM #OtherCharges where EntryDate>='2018-08-01' and EntryDate<='2018-08-07' ) derivedtable WHERE derivedtable.NewColumn IS NOT NULL order by NewColumn select * from @UpdateLog;
这篇关于在SQL Server中使用输出值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文