John W. Colby
jwcolby at colbyconsulting.com
Wed Sep 22 14:34:21 CDT 2004
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