June 10, 2003 at 10:47 am
Why doesn't this work
SELECT * FROM #NEWTEMP
UNION
SELECT
asg.ASGN_STAT_CODE,
asg.ACTUAL_START_DATE,
asg.ANTICIPATED_END_DATE,
PROGRAM.PROGRAM_NAME,
PERSON.LAST_NAME,
PERSON.FIRST_NAME,
HOSTCITY.DESCRIPTION AS HST_CITY,
HOSTCOUNTRY.DESCRIPTION AS HST_COUNTRY,
HOMECITY.DESCRIPTION AS HM_CITY,
HOMECOUNTRY.DESCRIPTION AS HM_COUNTRY,
bizdiv.DESCRIPTION componentname,
bizprnt.DESCRIPTION businessname,
13 as seqnum,
asg.assignment_id,
ah.effective_date as effdate,
sb.BILLING_HM_COSTCENTER as HOME_COST_CENTER,
sb.BILLING_HS_COSTCENTER as HOST_COST_CENTER,
bp.RELATED_ACCT as BILLING_ARE_NO,
bp.ORGANIZATION_ID as BILLING_ORG_ID,
sb.BILLING_AKZ_NO,
null as key1,
null as key2,
null as key3,
'A' as FeeCodeFlag,
-- cast(ah.VALUE as FLOAT) as amt
ah.VALUE as amt
FROM ASSIGNMENT_HISTORY ah
inner join ASSIGNMENT asg on ah.ASSIGNMENT_ID=asg.ASSIGNMENT_ID
inner join PERSON on asg.EXPATRIATE_PERSON_ID= PERSON.PERSON_ID
LEFT JOIN SAG_BILLING sb on asg.ASSIGNMENT_ID=sb.ASSIGNMENT_ID
LEFT JOIN PROGRAM on asg.program_id=program.program_id
LEFT JOIN BUSINESS_PLACE as hostbp on asg.host_business_place_id=hostbp.business_place_id
LEFT JOIN BUSINESS_PLACE as homebp on asg.home_business_place_id=homebp.business_place_id
LEFT JOIN PLACE AS HOSTCOUNTRY on hostbp.secure_country_place_id=hostcountry.place_id
LEFT JOIN PLACE AS HOMECOUNTRY on homebp.secure_country_place_id=homecountry.place_id
LEFT JOIN PLACE AS HOSTCITY on hostbp.city_place_id=hostcity.place_id
LEFT JOIN PLACE AS HOMECITY on homebp.city_place_id=homecity.place_id
LEFT JOIN BUSINESS AS HOME_COMPONENT on Homebp.BUSINESS_ID = HOME_COMPONENT.BUSINESS_Id
LEFT JOIN BUSINESS AS HOST_COMPONENT on Hostbp.BUSINESS_ID = HOST_COMPONENT.BUSINESS_Id
LEFT JOIN BUSINESS AS HOME_BUSINESS on HOME_COMPONENT.PARENT_BUSINESS_ID = HOME_BUSINESS.BUSINESS_ID
LEFT JOIN BUSINESS AS HOST_BUSINESS on HOST_COMPONENT.PARENT_BUSINESS_ID = HOST_BUSINESS.BUSINESS_ID
LEFT JOIN BUSINESS_PLACE bp ON sb.BILLING_BUSINESS_PLACE_ID=bp.BUSINESS_PLACE_ID
LEFT JOIN BUSINESS bizdiv ON bp.BUSINESS_ID=bizdiv.BUSINESS_ID
LEFT JOIN BUSINESS bizprnt ON bp.SECURE_BUSINESS_BUSINESS_ID=bizprnt.BUSINESS_ID
where ah.FIELD_NAME='SPECIAL PROJECTS' --AND ah.Effective_Date BETWEEN cast(@Begindate as datetime) and cast(@enddate as datetime)
INTO #MYTAB
John Zacharkan
John Zacharkan
June 10, 2003 at 12:09 pm
Try specifying the returned fields in the SELECT * FROM #NEWTEMP. I believe the UNION requires a matching number of fields in each of the joined resultsets.
June 10, 2003 at 12:31 pm
It works without the INTO, you don't need to specify the fields. You just need the number of fields and data types match.
quote:
Try specifying the returned fields in the SELECT * FROM #NEWTEMP. I believe the UNION requires a matching number of fields in each of the joined resultsets.
John Zacharkan
John Zacharkan
June 10, 2003 at 12:41 pm
Is there an error code produced. "It doesn't work" doesn't help me much. If I read your code correctly, you are trying to insert a temp table's records into another; have you tried reversing the order of the UNION? I would imagine the INTO statement might be thrown off by the *. This statement should accomplish the exact same thing:
SELECT
asg.ASGN_STAT_CODE,
asg.ACTUAL_START_DATE,
asg.ANTICIPATED_END_DATE,
PROGRAM.PROGRAM_NAME,
PERSON.LAST_NAME,
PERSON.FIRST_NAME,
HOSTCITY.DESCRIPTION AS HST_CITY,
HOSTCOUNTRY.DESCRIPTION AS HST_COUNTRY,
HOMECITY.DESCRIPTION AS HM_CITY,
HOMECOUNTRY.DESCRIPTION AS HM_COUNTRY,
bizdiv.DESCRIPTION componentname,
bizprnt.DESCRIPTION businessname,
13 as seqnum,
asg.assignment_id,
ah.effective_date as effdate,
sb.BILLING_HM_COSTCENTER as HOME_COST_CENTER,
sb.BILLING_HS_COSTCENTER as HOST_COST_CENTER,
bp.RELATED_ACCT as BILLING_ARE_NO,
bp.ORGANIZATION_ID as BILLING_ORG_ID,
sb.BILLING_AKZ_NO,
null as key1,
null as key2,
null as key3,
'A' as FeeCodeFlag,
-- cast(ah.VALUE as FLOAT) as amt
ah.VALUE as amt
FROM ASSIGNMENT_HISTORY ah
inner join ASSIGNMENT asg on ah.ASSIGNMENT_ID=asg.ASSIGNMENT_ID
inner join PERSON on asg.EXPATRIATE_PERSON_ID= PERSON.PERSON_ID
LEFT JOIN SAG_BILLING sb on asg.ASSIGNMENT_ID=sb.ASSIGNMENT_ID
LEFT JOIN PROGRAM on asg.program_id=program.program_id
LEFT JOIN BUSINESS_PLACE as hostbp on asg.host_business_place_id=hostbp.business_place_id
LEFT JOIN BUSINESS_PLACE as homebp on asg.home_business_place_id=homebp.business_place_id
LEFT JOIN PLACE AS HOSTCOUNTRY on hostbp.secure_country_place_id=hostcountry.place_id
LEFT JOIN PLACE AS HOMECOUNTRY on homebp.secure_country_place_id=homecountry.place_id
LEFT JOIN PLACE AS HOSTCITY on hostbp.city_place_id=hostcity.place_id
LEFT JOIN PLACE AS HOMECITY on homebp.city_place_id=homecity.place_id
LEFT JOIN BUSINESS AS HOME_COMPONENT on Homebp.BUSINESS_ID = HOME_COMPONENT.BUSINESS_Id
LEFT JOIN BUSINESS AS HOST_COMPONENT on Hostbp.BUSINESS_ID = HOST_COMPONENT.BUSINESS_Id
LEFT JOIN BUSINESS AS HOME_BUSINESS on HOME_COMPONENT.PARENT_BUSINESS_ID = HOME_BUSINESS.BUSINESS_ID
LEFT JOIN BUSINESS AS HOST_BUSINESS on HOST_COMPONENT.PARENT_BUSINESS_ID = HOST_BUSINESS.BUSINESS_ID
LEFT JOIN BUSINESS_PLACE bp ON sb.BILLING_BUSINESS_PLACE_ID=bp.BUSINESS_PLACE_ID
LEFT JOIN BUSINESS bizdiv ON bp.BUSINESS_ID=bizdiv.BUSINESS_ID
LEFT JOIN BUSINESS bizprnt ON bp.SECURE_BUSINESS_BUSINESS_ID=bizprnt.BUSINESS_ID
WHERE ah.FIELD_NAME='SPECIAL PROJECTS' --AND ah.Effective_Date BETWEEN cast(@Begindate as datetime) and cast(@enddate as datetime)
UNION
SELECT * FROM #NEWTEMP
INTO #MYTAB
June 10, 2003 at 8:11 pm
It doesn't work because your syntax is wrong!
The INTO TableName clause belongs immediately after the first SELECT clause and before the first FROM clause. Thus, your example of:
SELECT a,b,c
FROM t1
UNION
SELECT x,y,z
FROM t2
INTO ttemp
should be rewritten as:
SELECT a,b,c
INTO ttemp
FROM t1
UNION
SELECT x,y,z
FROM t2
Edited by - Paul Thornett on 06/10/2003 8:12:14 PM
June 10, 2003 at 8:50 pm
Just to help: This insert the result of Union to the temp table
SELECT *
INTO ttemp
FROM (
SELECT a,b,c
FROM t1
UNION
SELECT x,y,z
FROM t2
)
June 11, 2003 at 9:09 am
I guess I should just stick to Admin work eh, It took a little bit, but I figure it out yesterday. BOL doesn't exactly give you those type of examples. Thanks Paul and mlwang for your examples.
cheers
John Zacharkan
John Zacharkan
June 11, 2003 at 9:13 am
One note on all this - any statement that contains a #table that appears before the #table gets created, will cause the SPROC to recompile on each run. It is more efficient to create the #table first and then insert into .
thanks all
John Zacharkan
John Zacharkan
June 11, 2003 at 3:46 pm
I am curious about inserting into a table.
Is it better practice to do a 'INSERT INTO <Table Name> SELECT <fields> FROM etc..'
instead of 'SELECT <fields> INTO <Table Name> FROM etc...'
June 11, 2003 at 6:04 pm
>One note on all this - any statement that contains a #table that
>appears before the #table gets created, will cause the SPROC to
>recompile on each run. It is more efficient to create the #table
>first and then insert into .
Yes, everyone says this. But I'm really not at all convinced about this. It's certainly true that the SPROC will be recompiled every time. But I find INSERT INTO vastly slower than SELECT INTO.
Here's an example:
A) SELECT StudentId INTO ttemp1 FROM t1 WHERE EffYear=2001 (this produces 61,857 rows)
B) CREATE TABLE ttemp2 (StudentId int)
INSERT ttemp2 SELECT StudentId FROM t1 WHERE EffYear=2001
Example A takes 0.349 seconds,
with Scan Count = 1, Logical Reads = 104, Physical Reads = 2, Read-ahead Reads = 101
Example B takes 29.646 seconds
with Scan Count = 1, Logical Reads = 61,961, Physical Reads = 2, Read-ahead Reads = 101
So unless the SPROC takes more than 29.297 seconds to recompile, I don't see the benefit of the INSERT INTO.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply