jwcolby
jwcolby at colbyconsulting.com
Thu Aug 6 07:20:39 CDT 2009
I used Stuart's selection and it works. I then built it into a stored procedure with parameters, built up dynamic SQL and am now happy as a clam. Thanks for the response. John W. Colby www.ColbyConsulting.com Mark Breen wrote: > Hello John, > See also Michael Maddisons reply. > > It is very easy to use temp tables, and one day you will need to do a Update > and will have multiple joins in the tables to be updated, then that SQL > becomes more complex and difficult to get clear in the head. When that > happens, Mike temp table works a treat, you can build the big select and > insert into a temp table, then once the hard bit is done you can do a simple > update by joining to the temp table. > > In this case Stuarts solution is probably simpler, but store Mikes idea, it > is the same we all use to do in Access, but linking queries to previously > saved queries. but it also usually gives great performance for complex > queries. > > I have an instinct, let anyone confirm this if it is true, that Oracle heads > use temp tables much more that SQL Server heads. In fact, I believe that > Cursors are also used extensively in the Oracle world. > > Thanks > > Mark > > > > 2009/8/6 jwcolby <jwcolby at colbyconsulting.com> > >> Thanks Stuart. >> >> John W. Colby >> www.ColbyConsulting.com >> >> >> Stuart McLachlan wrote: >>> This won't work before 2005.- before then you couldn't use an expression >> for TOP: >>> declare @topnumber int >>> declare @NoKids int >>> >>> set @topnumber = 10000 >>> set @NoKids = 2 >>> >>> update tblOrderData >>> set keycode = 'KEYA' >>> where RandomNumber in >>> (Select top (@topnumber) RandomNumber >>> From tblOrderData >>> Where NoChildren = @NoKids and Keycode is null >>> Order By RandomNumber) >>> >>> On 5 Aug 2009 at 18:31, jwcolby wrote: >>> >>>> Ooops, sorry, SQL Server 2005. >>>> >>>> John W. Colby >>>> www.ColbyConsulting.com >>>> >>>> >>>> Stuart McLachlan wrote: >>>>> Jet SQL or SQL Server? >>>>> >>>> -- >>>> AccessD mailing list >>>> AccessD at databaseadvisors.com >>>> http://databaseadvisors.com/mailman/listinfo/accessd >>>> Website: http://www.databaseadvisors.com >>> >>> Stuart McLachlan >>> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >>