Arthur Fuller
fuller.artful at gmail.com
Wed Jun 20 11:28:52 CDT 2007
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 > >