Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Union query to temp table Expand / Collapse
Author
Message
Posted Monday, March 14, 2011 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 18, 2011 8:21 AM
Points: 7, Visits: 18
Hi all,

I've written a union query to extact data from our ERP solution but want to write this into a temporary table for further manipulation. What's the best way of doing this?

My query thus far is;


Use Evision_Latest

Declare @Contract varchar(50)
Declare @EndDate varchar(50)

Select @Contract = 'CW0455'
Select @EndDate = '2010-10-31'

Select
j.[Job No_] 'ContractNumber',
j.[Posting Date] as 'PostingDate',
j.[Total Price]+j.[Total Cost] as 'Amount',
j.[Global Dimension 2 Code] as 'CostCode',
'JNL01' as 'SourceCode',
d.[Name] as 'CostCodeName',
'Journal' as 'SourceName',
j.[Shortcut Dimension 6 Code] as 'ResourceCode',
j.[Global Dimension 2 Code] + j.[Shortcut Dimension 6 Code] as 'Key'
from
[Swift horsman Ltd$Job Ledger Entry] as j
inner join
[Swift horsman Ltd$Dimension Value] as d
on j.[Global Dimension 2 code] = d.[code]
where
j.[Job No_] = @Contract
and
j.[Posting Date] >= @EndDate
and
j.[Source Type] = '4'
and
j.[Global Dimension 2 Code] >= '10000'
and
j.[Global Dimension 2 Code] <= '99999'
UNION ALL
Select
j.[Job No_] 'ContractNumber',
j.[Posting Date] as 'PostingDate',
j.[Total Price]+j.[Total Cost] as 'Amount',
j.[Global Dimension 2 Code] as 'CostCode',
'LAB01' as 'SourceCode',
d.[Name] as 'CostCodeName',
'Direct Labour' as 'SourceName',
'' as 'ResourceCode',
j.[Global Dimension 2 Code] + 'LAB01' as 'Key'
from
[Swift horsman Ltd$Job Ledger Entry] as j
inner join
[Swift horsman Ltd$Dimension Value] as d
on j.[Global Dimension 2 code] = d.[code]
where
j.[Job No_] = @Contract
and
j.[Posting Date] >= @EndDate
and
j.[Source Type] = '0'
and
j.[Global Dimension 2 Code] >= '10000'
and
j.[Global Dimension 2 Code] <= '99999'
UNION ALL
Select
j.[Job No_] 'ContractNumber',
j.[Posting Date] as 'PostingDate',
j.[Total Price]+j.[Total Cost] as 'Amount',
j.[Global Dimension 2 Code] as 'CostCode',
j.[Source no_] as 'SourceCode',
d.[Name] as 'CostCodeName',
s.[Name] as 'SourceName',
'' as 'ResourceCode',
j.[Global Dimension 2 Code] + j.[Source no_] as 'Key'
from
[Swift horsman Ltd$Job Ledger Entry] as j
inner join
[Swift horsman Ltd$Dimension Value] as d
on j.[Global Dimension 2 code] = d.[code]
inner join
[Swift Horsman Ltd$Subcontractor] as s
on s.[No_] =j.[Source No_]
where
j.[Job No_] = @Contract
and
j.[Posting Date] >= @EndDate
and
j.[Source Type] = '3'
and
j.[Global Dimension 2 Code] >= '10000'
and
j.[Global Dimension 2 Code] <= '99999'
UNION ALL
Select
j.[Job No_] 'ContractNumber',
j.[Posting Date] as 'PostingDate',
j.[Total Price]+j.[Total Cost] as 'Amount',
j.[Global Dimension 2 Code] as 'CostCode',
j.[Source no_] as 'SourceCode',
d.[Name] as 'CostCodeName',
v.[Name] as 'SourceName',
'' as 'ResourceCode',
j.[Global Dimension 2 Code] + j.[Source no_] as 'Key'
from
[Swift horsman Ltd$Job Ledger Entry] as j
inner join
[Swift horsman Ltd$Dimension Value] as d
on j.[Global Dimension 2 code] = d.[code]
inner join
[Swift Horsman Ltd$Vendor] as v
on v.[No_] =j.[Source No_]
where
j.[Job No_] = @Contract
and
j.[Posting Date] >= @EndDate
and
j.[Source Type] = '2'
and
j.[Global Dimension 2 Code] >= '10000'
and
j.[Global Dimension 2 Code] <= '99999'
Order by
j.[Global Dimension 2 Code] asc

Appreciate any advice.

Thanks,

Simon
Post #1077816
Posted Monday, March 14, 2011 10:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
Best is to create your temp table and then perform a regular insert into #temptb select .... yourunionquery

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1077851
Posted Monday, March 14, 2011 11:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 18, 2011 8:21 AM
Points: 7, Visits: 18
Thanks for the post.
Post #1077852
Posted Monday, March 14, 2011 11:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 12,755, Visits: 31,122
for reference though, the format is not too difficult: it's INTO #Tablename just before the first from
SELECT
ColumnList
INTO #TMP
FROM ATable
JOIN OtherTables ON ...
UNION
SELECT columnList
FromBTable



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1077861
Posted Monday, March 14, 2011 12:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
Lowell (3/14/2011)
for reference though, the format is not too difficult: it's INTO #Tablename just before the first from
SELECT
ColumnList
INTO #TMP
FROM ATable
JOIN OtherTables ON ...
UNION
SELECT columnList
FromBTable



Keep in mind, doing it this way may cause a lock on tempdb system tables for the whole duration of the query.
That's why I suggested to first create the temp table.

Tempdb is used by everyone that uses your instance !
(order by / group by / temp tb/ tb var / query set materialisation, ....)


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1077940
Posted Thursday, January 23, 2014 12:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 13, 2014 7:35 AM
Points: 3, Visits: 8
ya... Good Answer... Lowell
Post #1533922
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse