Mark Breen
marklbreen at gmail.com
Thu Aug 6 06:13:53 CDT 2009
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 >