Union query to temp table

Question

Post reply

Union query to temp table

s.green

SSC Veteran

Points: 299
More actions
March 14, 2011 at 9:57 am

#99193

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.
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.
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.
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.
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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135258 More actions · Answer 1

Best is to create your temp table and then perform a regular insert into #temptb select .... yourunionquery

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

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

- 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

s.green SSC Veteran Points: 299 More actions · Answer 2

s.green

SSC Veteran

Points: 299

March 14, 2011 at 11:00 am

#1298451

Thanks for the post.

Lowell SSC Guru Points: 323504 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Johan Bijnens SSC Guru Points: 135258 More actions · Answer 4

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 !