[dba-SQLServer] Building a permanent result table on-the-fly

Robert rl_stewart at highstream.net
Wed Jun 20 11:27:18 CDT 2007


Off the top of my head...

SELECT INTO NewTable
TOP (3000) [FName], [LName], [Addr], [City], [ST], [Zip5],
[Zip4], [Zip]
FROM         dbo.tblData
ORDER BY NEWID()

Robert

At 11:19 AM 6/20/2007, you wrote:
>Date: Wed, 20 Jun 2007 11:45:56 -0400
>From: "jwcolby" <jwcolby at colbyconsulting.com>
>Subject: [dba-SQLServer] Building a permanent result table on-the-fly
>To: <dba-sqlserver at databaseadvisors.com>
>Message-ID: <20070620154556.D79A5BE73 at smtp-auth.no-ip.com>
>Content-Type: text/plain;       charset="us-ascii"
>
>When a client asks me to fill an order of names from a name table I end up
>with a view that looks something like:
>
>SELECT     TOP (3000) [FName], [LName], [Addr], [City], [ST], [Zip5],
>[Zip4], [Zip]
>FROM         dbo.tblData
>ORDER BY NEWID()
>
>This in fact pulls an apparently random set of names which is good, we like
>that.  The problem is that I need to record those names because the client
>may come back in a month and say "now give me a DIFFERENT set of names".
>Thus I need to know who was pulled last time.  What I need is a way to take
>any result set and build a permanent table to hold the results in.  I also
>need a way to build a m-m with the PKID of the pulled set and an OrderPK
>from an order table (which I am building), but I already know how to do
>that.
>
>John W. Colby





More information about the dba-SQLServer mailing list