July 6, 2009 at 7:11 am
Currently I have a cursor which returns 1000’s of distinct ID’s In this cursor I selects from a table with multiple entries for that ID its order by date so it only processes the older one.
I was wondering if this could be done with out a cursor?
current example
Cursor test = select distinct ID from aaa
/*Result
1
2*/
Fetch ID
Select @aa=aa,@bb=bb from bb order by date
Insert @aa,@bb into ccc
/*results
1,aa
1,bb
1,cc
1,dd*/
Next
July 6, 2009 at 7:26 am
Yep, an INSERT..SELECT. Try it like this:
INSERT Into ccc(caa, cbb)
SELECT aa, bb
FROM bb
I think you'll agree, that's a bit easier. And it's loads faster.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 6, 2009 at 9:42 am
Hi thanks for that but I only want the oldest record for each ID. That is why I am using the cursor. get all the disinct IDs from table AAA and then cycle through tables BBB using the ID's and only insert the oldest. Hope that makes more sense?
July 6, 2009 at 9:46 am
There really isn't enough information to provide a really good answer. Could you provide the DDL for the table(s) involved, sample data (in a readily consummable format that can be cut/past/run in SSMS or QA), and expected results based on the sample data?
For help on this, please read and follow the guidelines in the first article I reference below in my signature block regarding asking for assistance.
July 6, 2009 at 9:59 am
Its only a theory it should be applicable to things which match the condition
1 the cursor selects only distinct ID's
2 each steps it selects from a different table using that ID but only inserts the oldest record into a third table
As far as I can see if I use a join all records from table 2 with the ID will be inserted into the third table not just the oldest.
July 6, 2009 at 10:01 am
Edward (7/6/2009)
Its only a theory it should be applicable to things which match the condition1 the cursor selects only distinct ID's
2 each steps it selects from a different table using that ID but only inserts the oldest record into a third table
As far as I can see if I use a join all records from table 2 with the ID will be inserted into the third table not just the oldest.
Theory is great, but show me something concrete to work with to demonstrate. If you aren't willing to put in some effort, why should anyone else?
How do I determine which record is the oldest? Date? Record Number?
July 6, 2009 at 3:48 pm
Here is one guess. You might be able to emilenate the join between AAA and BBB, depends on your data...INSERT CCC
SELECT *
FROM
(
SELECT T2.*
FROM BBB AS B
INNER JOIN
(
SELECTB.ID, MAX(B.Date) AS MaxDate
FROM BBB AS B
INNER JOIN AAA AS A
ON B.ID = A.ID
GROUP BY B.D
) AS T
ON B.ID = T.ID
AND B.Date = T.MaxDate
) AS D
July 6, 2009 at 7:20 pm
Edward (7/6/2009)
Hi thanks for that but I only want the oldest record for each ID. That is why I am using the cursor. get all the disinct IDs from table AAA and then cycle through tables BBB using the ID's and only insert the oldest. Hope that makes more sense?
Let's see if I have it...
-- remove temp tables if they already exist
if object_id('tempdb..#bb') is not null drop table #bb
if object_id('tempdb..#ccc') is not null drop table #ccc
-- NOTE ---
-- see how I create some temporary tables and fill them with some test data?
-- If you had supplied this, it would have made it a LOT easier for people to help you.
-- The table definitions tells us a lot of information... datatypes, indexes, etc.
create table #bb (aa int, bb int, UpdDate datetime)
create table #ccc (aa int, bb int)
-- insert some test data into the temp tables.
insert into #bb
select 1,1,'1/1/1970' union
select 1,1,'1/1/1971' union
select 1,2,'1/1/1972' union
select 1,3,'1/1/1973' union
select 2,1,'1/1/1980' union
select 2,1,'1/1/1981' union
select 2,2,'1/1/1982' union
select 2,3,'1/1/1983'
-- lets look at the test data
select * from #bb
-- NOTE --
-- what you need to do here is to supply expected output - based on the test data.
-- then, we can see EXACTLY what you are attempting to do.
-- here I join the table to itself, based on the id (aa) and the oldest date (min(UpdDate))
insert into #ccc
select t1.aa, t1.bb
from #bb t1
INNER JOIN (select aa, UpdDate = min(UpdDate) from #bb group by aa) t2
ON t1.aa = t2.aa and t1.UpdDate = t2.UpdDate
-- now, lets look at the results
select * from #ccc
Notice that the final code to get rid of your cursor was very tiny. It took more space to set up the test environment. Since you didn't provide this, I had to generate it. You could have saved all of us that time.
You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be. As a bonus to you, you will get tested code back.
For more details on how to get all of this into your post, please look at the link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 7, 2009 at 4:49 am
HI Wayne,
Thats great that eactly it!! Thanks so much in future I will post a sample.
Many thanks
July 7, 2009 at 6:08 am
HI Lamprey,
Sorry thanks for your help too.
Many thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply