jwcolby
jwcolby at colbyconsulting.com
Wed Jun 20 13:43:25 CDT 2007
tblOrder does not exist. I get an error saying Invalid object name 'tblOrder'. I have just done it in Access, took about 10 minutes from setup of the db through export of the text file. AND... The export spec is saved for the next one. When you just gotta get it done, right now, use Access. 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 Arthur Fuller Sent: Wednesday, June 20, 2007 12:29 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Building a permanent result table on-the-fly 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 > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com