Click here to Skip to main content
14,085,522 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
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
Posted
Updated 10-Sep-18 20:24pm
Comments
Santosh kumar Pithani 11-Sep-18 4:26am
   
your question is not clear..further "I want to use these output values" does
its make sense ?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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).
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

-- 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;
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.190518.1 | Last Updated 11 Sep 2018
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100