Arthur Fuller
fuller.artful at gmail.com
Wed Jun 20 11:32:31 CDT 2007
I think Dan has a point, and it exposes a possible weakness in my proffered solution: I'm restricted to "not in the last batch" whereas Dan's approach can exclude anyone who participated in any previous batch. A. On 6/20/07, Arthur Fuller <fuller.artful at gmail.com> wrote: > > INSERT INTO <newTable> > SELECT x, y, z FROM <yourFinalOrderView> > > (assuming that all the desired filtering etc. has already been done by the > time we get to yourFinalOrderView.) > > It would be good to have unique keys not dependent upon NewID(), in which > case the selection of a bunch of new folks not previously included is: Outer > Join on the keys and criteria = "WHERE oldBatch.ID IS NULL". > > Unless I misunderstood your requirement. > > Assuming that I am right... no, wait, I will wait for confirmation on that > hypothesis before continuing. > > A. > > On 6/20/07, jwcolby <jwcolby at colbyconsulting.com> wrote: > > > > A variety of reasons: > > > > 1) The data may come from several different tables. For example the > > data I > > am pulling now comes from two different tables, the first is used until > > they > > have all been used, then the second table is used to fill in the rest. > > 2) There may be a hundred different orders against any given address > > table > > over the course of a year. > > 3) In the end the data is often a join of at two and sometimes three > > different tables. For example I have a ZipCodeWorld table with section > > codes, joined by Zip on tblAZTFFSmokers (address validated table) joined > > by > > PK on tblTFFSmokers (demographics table). > > > > The end results are ALWAYS pulled from a set of views, joined into at > > least > > one final "order" view. I do not send my PK or address hash to the > > client, > > they don't need nor want that data in their text file. But the PK as > > well > > as the name of the table I pulled from needs to be preserved so that I > > can > > filter it back out later. I am working on that, however what I would > > like > > for now is a simple "stuff the data in a table" thing so that I at least > > know what I sent. This result set comes from a view. In Access you can > > use > > a query to build a table. Access somehow discovers the data types and > > (mostly) correctly builds a table out of the data set. I do not know > > how to > > do that in SQL Server. I suppose I can go do that in Access just to get > > off > > the dime on this, and revisit it next week when the pressure is off. > > > > John W. Colby > > Colby Consulting > > www.ColbyConsulting.com > > -----Original Message----- > > From: dba-sqlserver-bounces at databaseadvisors.com > > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Eric > > Barro > > Sent: Wednesday, June 20, 2007 11:51 AM > > To: dba-sqlserver at databaseadvisors.com > > Subject: Re: [dba-SQLServer] Building a permanent result table > > on-the-fly > > > > Why not just flag the records with a datestamp and add that in the > > criteria > > so that it only displays records that have not been pulled from the > > previous > > query? > > > > -----Original Message----- > > From: dba-sqlserver-bounces at databaseadvisors.com > > [mailto: dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of > > jwcolby > > Sent: Wednesday, June 20, 2007 8:46 AM > > To: dba-sqlserver at databaseadvisors.com > > Subject: [dba-SQLServer] Building a permanent result table on-the-fly > > > > 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 > > Colby Consulting > > www.ColbyConsulting.com > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > > > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > >