jwcolby
jwcolby at colbyconsulting.com
Wed Jun 20 11:06:55 CDT 2007
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