SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Performance		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

faijurrahuman
Starting Member

India
15 Posts
Posted - 03/13/2012 :  00:35:02  Show Profile  Reply with Quote
Dear all;
Table having 1 million record we have to write the quries like this .

the number of union all to write and finally insert the new table. in case more then 500 union all sql stmt writing and execute . but it take an more time to perform this task .
any one help this case..

Example

SELECT @pubID,
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '608' AND entity_id = @progID),
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '11606' AND entity_id = @orgID),
GETDATE(),@userId,@relSupp,1,'Fuel cooled oil cooler'
UNION ALL
SELECT @pubID,
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '12569' AND entity_id = @orgID ),
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '608' AND entity_id = @progID ),
GETDATE(),@userId,@relSuppFor,1,'Placards; ID plated markings'
UNION ALL
SELECT @pubID,
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '608' AND entity_id = @progID),
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '12569' AND entity_id = @orgID),
GETDATE(),@userId,@relSupp,1,'Placards; ID plated markings'
....etc

Thanks





Share Knowledge team

khtan
In (Som, Ni, Yak)

Singapore
16766 Posts
Posted - 03/13/2012 :  00:55:41  Show Profile  Reply with Quote
you can change 

SELECT @pubID,
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '608' AND entity_id = @progID),
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '11606' AND entity_id = @orgID),
GETDATE(),@userId,@relSupp,1,'Fuel cooled oil cooler'

to

select	@pubID,
	max(case when acc_no = '608' then entity_item_id end),
	max(case when acc_no = '11606' then entity_item_id end),
	GETDATE(), @userId, @relSupp, 1, 'Fuel cooled oil cooler'
from	#TMPTABLE
where	acc_no		in ('608', '11606')
and	entity_id	= @progID


does all your union query basically the same ? only the acc_no = 'xxx' part that varies ?

KH
Time is always against us
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000