March 14, 2011 at 9:57 am
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
March 14, 2011 at 10:55 am
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
March 14, 2011 at 11:00 am
Thanks for the post.
March 14, 2011 at 11:04 am
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
March 14, 2011 at 12:37 pm
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
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
January 23, 2014 at 12:28 am
ya... Good Answer... Lowell
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy