Francisco Tapia
fhtapia at gmail.com
Wed Sep 22 21:58:18 CDT 2004
A very good point... the Join clause is going to be MUCH faster in this situation.... thanks Arthur. On Wed, 22 Sep 2004 20:13:14 -0400, Arthur Fuller <artful at rogers.com> wrote: > That will probably work but with that many rows it's going to take days. > Try a JOIN instead, JC, something along the lines of: > > Select * from T1 OUTER JOIN T2 on T1.PK = T2.PK WHERE T2.AddressColumn > IS NULL; > > A. > > > > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of > Francisco Tapia > Sent: Wednesday, September 22, 2004 4:09 PM > To: dba-sqlserver at databaseadvisors.com > Subject: Re: [dba-SQLServer] Iif in SQL Server > > A solution is for your APPEND query (SELECT statement) to include a > clause that excludes the PK's > > Something like SELECT FIeld1, Field2, ... > FROM table > WHERE PK NOT EXISTS(SELECT PK From PrimaryTable) > > On Wed, 22 Sep 2004 15:34:21 -0400, John W. Colby > <jwcolby at colbyconsulting.com> wrote: > > It would work IF I could get a second set of records to append to the > > table. ATM the second append query immediately bombs with the > > "violation of primary key constraint" error. It appears that the > > second append (correctly) attempts to append SOME records with a PK > > already in the table. What I want is for the append query to silently > > > move on to the next record when that happens until it finds records > > where the PK ISN'T already in the table, then it appends THOSE > > records. What happens is that the entire query halts because of the > > first PK collision. > > > > John W. Colby > > www.ColbyConsulting.com > > > > > > > > -----Original Message----- > > From: dba-sqlserver-bounces at databaseadvisors.com > > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of > > Francisco Tapia > > Sent: Wednesday, September 22, 2004 3:20 PM > > To: dba-sqlserver at databaseadvisors.com > > Subject: Re: [dba-SQLServer] Iif in SQL Server > > > > So is that gonna work for you? > > > > On Wed, 22 Sep 2004 14:48:10 -0400, John W. Colby > > <jwcolby at colbyconsulting.com> wrote: > > > No, I don't want a report, at least not atm. I need the ability to > > > count these things over and over and over. Thus it has to be FAST. > > > Clients call and ask "how many addresses in these 230 zip codes? > > > That kind of stuff. The zips can come from two different places, > > > thus I want to pull them out and place them in a single table where > > > I can tell where they came from if I need to but I only have to do a > > > > count on a single indexed column. > > > > > > John W. Colby > > > www.ColbyConsulting.com > > > > > > > > > > > > -----Original Message----- > > > From: dba-sqlserver-bounces at databaseadvisors.com > > > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of > > > Francisco Tapia > > > Sent: Wednesday, September 22, 2004 2:06 PM > > > To: dba-sqlserver at databaseadvisors.com > > > Subject: Re: [dba-SQLServer] Iif in SQL Server > > > > > > You want a report right? ie a resultset... yes a sproc John.. you > > > don't have to CREATE a sproc, but doing so will store the > > > optimizations for the sproc in the server and possibly even caching > > > parts of the report (since the data hasn't changed). > > > > > > to create a sproc > > > > > > CREATE PROCEDURE stp_MyNewSprocNamingConvention > > > > > > AS > > > > > > SELECT FIELD1, Field2, Field3, Case... > > > FROM Table1 > > > WHERE ClauseoptionsHere > > > > > > On Wed, 22 Sep 2004 08:39:44 -0400, John W. Colby > > > <jwcolby at colbyconsulting.com> wrote: > > > > Are we talking about code in an Sproc here? I am not talking > > > > about AccessVB. I am looking for something to work with 165 > > > > million records and return results in a few minutes. > > > > > > > > In the end I built a table with a PK field, a ZIPCode field and a > > > > Src field. I made the PK field a PK so that it has a unique index. > > > > > I then wrote two queries, one that pulls data from one field plus > > > > the PK plus a 1 as Src and appends it to my new table. The other > > > > query pulls data from the other field plus the PK plus a 2 as the > > > > Src and appends to the table. Run the first query. All records > > > > that have anything in the ZIP code are put in the table. Run the > > > > second query. All the records that have something in the second > > > > ZIP field but aren't already in the table get put in the table. > > > > > > > > Now I have a table with ONE field, with data from one or the other > > > > > field, with a column which tells me which source field it came > > > > from, with a PK to join back up to the main table. > > > > > > > > Of course my second query is failing to append at all because the > > > > PK already exists in the new table. > > > > > > > > John W. Colby > > > > www.ColbyConsulting.com > > > > > > > > > > > > > > > > -----Original Message----- > > > > From: dba-sqlserver-bounces at databaseadvisors.com > > > > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of > > > > Francisco Tapia > > > > Sent: Wednesday, September 22, 2004 1:30 AM > > > > To: dba-sqlserver at databaseadvisors.com > > > > Subject: Re: [dba-SQLServer] Iif in SQL Server > > > > > > > > JOHN! > > > > > > > > I know you're busy these days but it works like this > > > > > > > > SELECT CASE WHEN FIELD = 'value' THEN FieldWhenTrue > > > > WHEN Fieldothercase = 'othervalue THEN > > > > FieldWhenOtherValue > > > > ELSE FieldwhenELSE > > > > END AS AliasName, > > > > Next Field > > > > > > > > >From TableName > > > > > > > > On Wed, 22 Sep 2004 11:45:36 +1000, Stuart McLachlan > > > > <stuart at lexacorp.com.pg> wrote: > > > > > On 21 Sep 2004 at 21:38, John W. Colby wrote: > > > > > > > > > > > Sorry, my Outlook is totally screwed up > > > > > > > > > > That's tautology. <grin> > > > > -- > > -Francisco > > http://ft316db.VOTEorNOT.org > > _______________________________________________ > > 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 > > > > > > -- > -Francisco > http://ft316db.VOTEorNOT.org > > > _______________________________________________ > 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 > > -- -Francisco http://ft316db.VOTEorNOT.org